MongoDB query - look up ObjectIDs in nested arrays of objects

Question

I need to look up ObjectIDs in nested arrays of objects. Here is what I got so far:

.Shift.aggregate([ 
            { $match: { startDate: { $gte:  lowerDate, $lte: upperDate} } }, 
            { $group: {_id: '$team', shifts: { $addToSet: '$_id' } } },
            { $lookup: {from: 'teams', localField: '_id', foreignField: '_id', as: 'team' }},
            { $lookup: {from: 'shifts', localField: 'shifts', foreignField: '_id', as: 'shifts' }}])

        .exec(function (err, teamShifts) {...

This is what i have now

[
  ...
  {
    "_id": "60a277b05c7462f42b3d788c",
    "shifts": [
      {
        "_id": "60a62116ead409441bd112",
        "assignedUser": "60a36baddf6a04c72b2d9f",
        "team": "60a277b05c7462f43d788c",
        "shiftType": "60a278357462f42b3d7895",
        "startDate": 1621641600000,
        "endDate": 1622246400000,
        "__v": 0
      },
      {
        "_id": "60a679a03525cec3f86949",
        "assignedUser":
        ...
      },
        ...
    ],
    "team": [
      {
        "_id": "60a277b05c62f42b3d788c",
        "name": "a team name",
        "owner": "60a26e6c91273a938690b2",
        "description": "a description",
      }
    ]
  },
   ...
]

for example I am trying to populate the "assignedUser" with the "_id" from another document "users"

But adding this does not produce the result I need:

{ $lookup: {from: 'users', localField: 'shifts.assignedUser', foreignField: '_id', as: 'shifts.assignedUser' }},

What do I need to do to lookup / populate IDs nested in the data?

I tried unwind but that makes it harder to group by team, also it's not need for this anymore as i read (we are running mongodb 4.4.3). I probably need piplines(?) but I have trouble using the docs for my case.

RoundedHouse · Accepted Answer · 2021-05-21 22:34:11Z

0

To get search through an array of documents in mongodb,elemMatch could make your life easier. Searching for and _id, in relation to your problem should be written as this:

{ $lookup: {from: 'users', localField: shifts:{$elemMatch:{_id://idvalue_here}}, 
 foreignField: '_id', as:'shifts.$.assignedUser'}}

answered May 21, 2021 at 22:34

RoundedHouse

813 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Over a year ago

Hi, thanks for answering. I can not get this to work, I always get syntax errors after "localField shifts" SyntaxError: Unexpected token ':''

{ $lookup: {from: 'users', localField: shifts:{$elemMatch:{_id: 'assignedUser'}}, foreignField: '_id', as:'shifts.$.assignedUser'}}

Collectives™ on Stack Overflow

MongoDB query - look up ObjectIDs in nested arrays of objects

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related