MongoDB: how to $lookup a field in an array? [duplicate]

Question

I am trying to retrieve some lookup data for an embedded array in a document. Here is a sample of the data:

[
   {
      "_id": "58a4fa0e24180825b05e14e9",
      "user_id": "589cf6511b94281f34617a13",
      "user": {
         "firstname": "John",
         "lastname": "Doe"
      },
      "stages": [
         {
            "user_id": "589cf6511b94281f34617a13"
         },
         {
            "user_id": "589cf6511b94281f346343c3"
         }
      ],
   }
]

As you see, stages is an array, containing user_id fields only. These are pointing to the _id field in another collection called users.

Here is how I got the above result:

db.collection('things').aggregate([{
        $match: finder
    },
    {
        $lookup: {
            from: 'users',
            localField: 'user_id',
            foreignField: '_id',
            as: 'user'
        }
    },
    {
        $unwind: '$user'
    }
]);

Now, this is pretty straightforward: a selection of records, with the user field looked up with $lookup. But how can I do the same with the elements of stages? The desired result would be this:

[
   {
      "_id": "58a4fa0e24180825b05e14e9",
      "user_id": "589cf6511b94281f34617a13",
      "user": {
         "firstname": "John",
         "lastname": "Doe"
      },

      "stages": [
         {
            "user_id": "589cf6511b94281f34617a13",
            "firstname": "John",
            "lastname": "Doe"
         },
         {
            "user_id": "589cf6511b94281f346343c3"
            "firstname": "Jane",
            "lastname": "Doe"
         }
      ],
   }
]

Answer 1

I believe what you're missing is an unwind in the stages and a group call after it. I tested the following code and got similar result:

db.collection('things').aggregate([
               { $match: finder },
               { $lookup: {  from: 'users',
                             localField: 'user_id',
                              foreignField: '_id',
                              as: 'user'
                           }
               },
               { $unwind: '$stages' },
               {$lookup : {from : 'users', localField: 'stages.user_id', foreignField: '_id', as : 'stages'}},
               {$group : { _id: '$_id', userId: "$userId",..., stages: {$push : "$stages"}}}
               ]);

Hope my code was useful