Pushing objects on a specific multidimensional mongoDb collection

Question

i'm fairly new to the mongoDb query language and I'm struggeling with following scenario.

We have a multidimensional dataset that is comprised of:

• n users
• n projects for each users
• n time_entries for each project

What I am trying to achieve is: I would like to push/update a time_entry of a specific project using a collection.update.

Note each pid should be unique for a user

The collection structure I am using looks as follows:

{
"_id" : ObjectId("5d6e33987f8d7f00c063ceff"),
"date" : "2019-01-01",
"users" : [ 
    {
        "user_id" : 1,
        "projects" : [ 
            {
                "pid" : 1,
                "time_entries" : [ 
                    {
                        "duration" : 1,
                        "start" : "2019-08-29T09:54:56+00:00"
                    }
                ]
            }, 
            {
                "pid" : 2,
                "time_entries" : []
            }
        ]
    },
    {
        "user_id" : 2,
        "projects" : [ 
            {
                "pid" : 3,
                "time_entries" : []
            }
         ]
    }
  ]
}

I'm currently able to update all projects of a given user using:

"users.$.projects.$[].time_entries" 

yet I'm not able to target a specific project, due to the fact the structure contains 2 nesting levels and using multiple $ positional operator is not yet permitted in MongoDb.

"users.$.projects.$.time_entries"

Below is my full query example:

db.times.update(
{ 'users' : { $elemMatch : { 'projects' : { $elemMatch : { 'pid' : 153446871 } }  } } },
{ "$push": 
    {
        "users.$.projects.$[].time_entries": 
        {
           "duration" : 5,
           "start" : "2019-08-29T09:54:56+00:00"
        }
    }
}

);

Are there other ways to achieve the same result?

• Should I flatten the array so I only use 1 $ positional operator?
• Are there other methods to push items on a multidimensional array?
• Should this logic be handled on a code level and not a Database level?

Answer 1

You'll need to use the Positional Filtered Operator to achieve that:

db.times.update(
    {}, 
    {
        $push: {
            "users.$[].projects.$[element].time_entries":{
                "duration" : 5, 
                "start" : "2019-08-29T09:54:56+00:00"
            }
        }
    },
    { 
        arrayFilters: [{"element.pid":1}], 
        multi: true
    }
)

This query will push data to the array time_entries for every pid = 1 it finds.

This will give you the result below:

{
    "_id" : ObjectId("5d6e33987f8d7f00c063ceff"),
    "date" : "2019-01-01",
    "users" : [ 
        {
            "user_id" : 1,
            "projects" : [ 
                {
                    "pid" : 1,
                    "time_entries" : [ 
                        {
                            "duration" : 1,
                            "start" : "2019-08-29T09:54:56+00:00"
                        }, 
                        {
                            "duration" : 5.0,
                            "start" : "2019-08-29T09:54:56+00:00"
                        }
                    ]
                }, 
                {
                    "pid" : 2,
                    "time_entries" : []
                }
            ]
        }, 
        {
            "user_id" : 2,
            "projects" : [ 
                {
                    "pid" : 3,
                    "time_entries" : []
                }
            ]
        }
    ]
}