MongoDB : fields are excluded when invoke multiple lookup stages-CodePudding

db={
  "dashboard": [
    {
      "_id": "dashboard1",
      "name": "test",
      "user": 1
    }
  ],
  "templatefolders": [
    {
      "dashboardId": "dashboard1",
      "folderId": "folder123",
      "name": "folder",
      "region": "XXX"
    }
  ],
  "folders": [
    {
      "_id": "folder123"
    }
  ],
  "user": [
    {
      "_id": 1,
      "name": "alaa"
    }
  ],
  
}

this is my function: 

db.dashboard.aggregate([
  {
    "$lookup": {
      "from": "templatefolders",
      "localField": "_id",
      "foreignField": "dashboardId",
      "as": "joinDashboard"
    }
  },
  {
    "$lookup": {
      "from": "folders",
      "localField": "joinDashboard.folderId",
      "foreignField": "_id",
      "as": "joinDashboard.joinFolder"
    }
  },
  
])

Result :

[
  {
    "_id": "dashboard1",
    "joinDashboard": {
      "joinFolder": [
        {
          "_id": "folder123"
        }
      ]
    },
    "name": "test",
    "user": 1
  }
]

[![enter image description here][1]][1]

Why the fields name and region in collection templatefolders are excluded ? I want to know why this behavior ? I don't like to use unwind because i have multiple collections with multiple refrence relation.

CodePudding user response：

Your second $lookup, is overriding the joinDashboard key completely. Since you want joinFolder to be within joinDashboard, you can try nested lookups like this:

db.dashboard.aggregate([
  {
    $lookup: {
      from: "templatefolders",
      let: {
        "boardId": "$_id"
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$dashboardId",
                "$$boardId"
              ]
            }
          }
        },
        {
          $lookup: {
            from: "folders",
            let: {
              "folderId": "$folderId"
            },
            pipeline: [
              {
                $match: {
                  $expr: {
                    $eq: [
                      "$_id",
                      "$$folderId"
                    ]
                  }
                }
              },
            ],
            as: "joinFolder"
          },
        },
      ],
      as: "joinDashboard"
    }
  }
])

MongoPlayground link.