I am new to MongoDB and I'm struggling to make a $lookup and get the desired result. If someone could help me, I would be very thankful

Collection Groups

{
  "_id": "any_id",
  "groups": [
    {
      "group_id": "group_id_1",
      "departments": [
        {
          "department_id": "id_1"
        },
        {
          "department_id": "id_2"
        }
      ]
    }
  ]
}

Collection Departments

{
  "_id": "id_1",
  "name": "name 1"
},
{
  "_id": "id_2",
  "name": "name 2"
},
{
  "_id": "id_3",
  "name": "name 3"
}

Expected Result

{
  "_id": "any_id",
  "groups": [
    {
      "group_id": "group_id_1",
      "departments": [
        {
          "department_id": "id_1",
          "name": "name 1"
        },
        {
          "department_id": "id_2",
          "name": "name 2"
        }
      ]
    }
  ]
}

CodePudding user response：

Here the solution: Demo@mongoplayground

Step 1: join the departments collection

Step 2: Map the resultant Array objects and merging according to condition

Mongodb aggregation Pipeline : https://mongoplayground.net/p/5ebIgizA3_8

db.workoutDetailSchema.aggregate([
  {
    $lookup: {
      from: "departments",
      localField: "groups.departments.department_id",
      foreignField: "_id",
      as: "result"
    }
  },
  {
    "$project": {
      "groups": {
        $map: {
          input: "$groups",
          as: "group",
          in: {
            $mergeObjects: [
              "$$group",
              {
                "departments": {
                  $map: {
                    input: "$$group.departments",
                    as: "dept",
                    in: {
                      $mergeObjects: [
                        "$$dept",
                        {
                          $first: {
                            $filter: {
                              input: "$result",
                              cond: {
                                $eq: [
                                  "$$dept.department_id",
                                  "$$this._id"
                                ]
                              }
                            }
                          }
                        }
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      },
    }
  },
  {
    $project: {
      "groups.departments._id": 0
    }
  }
])

Output:

[
  {
    "_id": "any_id",
    "groups": [
      {
        "departments": [
          {
            "department_id": "id_1",
            "name": "name 1"
          },
          {
            "department_id": "id_2",
            "name": "name 2"
          }
        ],
        "group_id": "group_id_1"
      }
    ]
  }
]