Populate to the deep level using $lookup mongodb

Question

I am using $lookup to join two collections and get data from below query:

let condition = {status:{$ne:config.PROJECT_STATUS.completed}, assignId:mongoose.Types.ObjectId(req.params.id)};
Project.aggregate([
    {
      $match: condition  
    },
    {
      "$group":{
        "_id": "$_id"
      }
    },
    { 
      "$lookup": {
        "from": "worksheets",
        "let": { "projectId": "$_id" },
        "pipeline": [
          { "$match": { "$expr": { "$eq": [ "$projectId", "$$projectId" ] }}},
          { "$group": {_id:"$projectId", totalHours:{"$sum": "$hours"}}},
          { 
            "$lookup": {
              "from": "projects",
              "let": { "projectId": "$_id" },
              "pipeline": [
                { "$match": { "$expr": { "$eq": [ "$_id", "$$projectId" ] }}},
                { "$project": { "projectName": 1,"upworkdId":1,"status":1,"developers":1,"hoursApproved":1 }}
              ],
              "as": "project"
            }
          }
        ],
        "as": "projects"
      }
    }
])
.then((data)=>{
    res.json(data);
})

And above query is giving me below result:

[
  {
    "_id": "5c0a4083753a321c6c4ee024",
    "projects": [
        {
            "_id": "5c0a4083753a321c6c4ee024",
            "totalHours": 11,
            "project": [
                {
                    "_id": "5c0a4083753a321c6c4ee024",
                    "hoursApproved": 24,
                    "developers": [
                        "5c0a29c697e71a0d28b910a9"
                    ],
                    "projectName": "fallbrook winery",
                    "status": "pending"
                }
            ]
        }
    ]
  }
]

Now i want to populate the developers subfield inside the project Array. How can i modify the above code to get that.

Answer 1

You can use $lookup one more deep level with the developers array.

Something like this

Project.aggregate([
  { "$match": condition },
  { "$group": { "_id": "$_id" }},
  { "$lookup": {
    "from": "worksheets",
    "let": { "projectId": "$_id" },
    "pipeline": [
      { "$match": { "$expr": { "$eq": ["$projectId", "$$projectId"] } } },
      { "$group": { "_id": "$projectId", "totalHours": { "$sum": "$hours" } }},
      { "$lookup": {
        "from": "projects",
        "let": { "projectId": "$_id" },
        "pipeline": [
          { "$match": { "$expr": { "$eq": ["$_id", "$$projectId"] } } },
          { "$lookup": {
            "from": "developers",
            "let": { "developers": "$developers" },
            "pipeline": [
              { "$match": { "$expr": { "$in": ["$_id", "$$developers"] } } },
            ],
            "as": "developers"
          }},
          { "$project": { 
            "projectName": 1, "upworkdId": 1, "status": 1, "developers": 1,  "hoursApproved": 1
          }}
        ],
        "as": "project"
      }}
    ],
    "as": "projects"
  }}
])