MongoDB : Add match to nested pipeline lookup

Question

I have this BD :

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

I have this query :

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"
    }
  }
])

I want to fetch all dashbords where dashbord.templateFolder.name=="folder" OR dashbord.templateFolder.folders.name=="test1" Please, how i can achieve this ? How i can add many conditions in the nested pipeline lookup, where one condition is related to another condition.

Answer 1

One option is use $lookup and then $match. In your case:

db.templatefolders.aggregate([
  {$lookup: {
      from: "folders",
      let: {"folderId": "$folderId"},
      pipeline: [
        {$match: {
            $expr: {$and: [
                {$eq: ["$_id", "$$folderId"]},
                {$eq: ["$name", "test1"]}
            ]}
        }}
      ],
      as: "folder"
    }
  },
  {$match: {$or: [{"folder.0": {$exists: true}}, {name: "folder"}]}},
  {$lookup: {
      from: "dashboard",
      as: "dashboard",
      localField: "dashboardId",
      foreignField: "_id"
  }},
  {$project: {_id: 0, dashboard: {$first: "$dashboard"}}},
  {$replaceRoot: {newRoot: "$dashboard"}}
])

See how it works on the playground example

You did not define your requested result, but you can use group if you have duplicates and you want to remove them.