MongoDB: How to populate the nested object with lookup query?

Question

I am fetching list of records having some nested reference to other collection, I want to populate that nested ObjectId which is inside array of objects, with mongoDb aggregate lookup query.

This is how DB collection structure is:

{
  subject: {type: String},
  body: {type: String},
  recipients: [{
    userId: {type: mongoose.Schema.Types.ObjectId, ref: 'User'},
    stutus: {type: String, enum: ['pending','accepted','rejected'], default:'pending'}
  }],
  sender: {type: mongoose.Schema.Types.ObjectId, ref: 'User'}
}

What I am expecting:

[{
  subject: 'Some subject here.',
  body: 'Lorem ipsum dolor emit set',
  recipients: [{
    userId: {firstName: 'John', lastName: 'Doe'},
    status: 'accepted'
  },{
    userId: {firstName: 'Jane', lastName: 'Doe'},
    status: 'accepted'
  }],
  sender: {firstName: 'Jacobs', 'lastName': 'Doe'}
},{
  subject: 'Some subject here.',
  body: 'Lorem ipsum dolor emit set',
  recipients: [{
    userId: {firstName: 'Jane', lastName: 'Doe'},
    status: 'rejected'
  },{
    userId: {firstName: 'John', lastName: 'Doe'},
    status: 'accepted'
  }],
  sender: {firstName: 'Jacobs', 'lastName': 'Doe'}
}]

Any kind help will be greatly appreciated.

Answer 1

Try This:

db.emails.aggregate([
    { $unwind: "$recipients" },
    {
        $lookup: {
            from: "users",
            let: { userId: "$recipients.userId", status: "$recipients.stutus" },
            pipeline: [
                {
                    $match: {
                        $expr: { $eq: ["$_id", "$$userId"] }
                    }
                },
                {
                    $project: {
                        "_id": 0,
                        "userId": {
                            "firstName": "$firstName",
                            "lastName": "$lastName",
                        },
                        "status": "$$status"
                    }
                }
            ],
            as: "recipient"
        }
    },
    {
        $lookup: {
            from: "users",
            let: { userId: "$sender" },
            pipeline: [
                {
                    $match: {
                        $expr: { $eq: ["$_id", "$$userId"] }
                    }
                },
                {
                    $project: {
                        "_id": 0,
                        "firstName": 1,
                        "lastName": 1
                    }
                }
            ],
            as: "sender"
        }
    },
    {
        $group: {
            _id: "$_id",
            subject: { $first: "$subject" },
            body: { $first: "$body" },
            recipients: { $push: { $arrayElemAt: ["$recipient", 0] } },
            sender: { $first: { $arrayElemAt: ["$sender", 0] } }
        }
    }
]);

Answer 2

• $unwind deconstruct recipients array
• $lookup with users collection for recipients.userId
• $unwind deconstruct recipients.userId array
• $lookup with users collection for sender
• $unwind deconstruct sender array
• $group by _id and reconstruct recipients array

db.mails.aggregate([
  { $unwind: "$recipients" },
  {
    $lookup: {
      from: "users",
      localField: "recipients.userId",
      foreignField: "_id",
      as: "recipients.userId"
    }
  },
  { $unwind: "$recipients.userId" },
  {
    $lookup: {
      from: "users",
      localField: "sender",
      foreignField: "_id",
      as: "sender"
    }
  },
  { $unwind: "$sender" },
  {
    $group: {
      _id: "$_id",
      recipients: { $push: "$recipients" },
      subject: { $first: "$subject" },
      body: { $first: "$body" },
      sender: { $first: "$sender" }
    }
  }
])

Playground