I am trying to make lookup in MongoDB nested array. My Data is looks like.

[
    {
        "_id": "621eedae92979fd8f0e9451d",
        "name": "Pallab Koley",
        "shifts": {
            "_id": "62636b9fcbda6d2b17f5cae0",
            "month": "2022-05",
            "shift": [
                {
                    "date": "2022-05-01",
                    "shiftId": "622bb0f4b88dc92e3c2cac56"
                },
                {
                    "date": "2022-05-02",
                    "shiftId": "622b55f8f59dcdd1ab9b36b1"
                },
            ]
        }
    },
    {
        "_id": "62626a7446ba9a911a623b37",
        "name": "Pinki Das",
        "shifts": {
            "_id": "62636ba4cbda6d2b17f5cae1",
            "month": "2022-05",
            "shift": [
                {
                    "date": "2022-05-01",
                    "shiftId": "622bb0f4b88dc92e3c2cac56"
                }
            ]
        }
    }
]

I was trying with the lookup.

{
    "$lookup": {
        "from": "shifts",
        "localField": "shifts.shift.shiftId",
        "foreignField": "_id",
        "as": "shifts.shift.shiftId"
    }
}

And getting the result.

[
  {
    "_id": "621eedae92979fd8f0e9451d",
    "name": "Pallab Koley",
    "shifts": {
      "_id": "62636b9fcbda6d2b17f5cae0",
      "month": "2022-05",
      "shift": {
        "date": "2022-05-01",
        "shiftId": [
          {
            "_id": "622bb0f4b88dc92e3c2cac56",
            "date": "2022-05-01",
            "name": "Day"
          }
        ]
      }
    }
  },
  {
    "_id": "621eedae92979fd8f0e9451d",
    "name": "Pallab Koley",
    "shifts": {
      "_id": "62636b9fcbda6d2b17f5cae0",
      "month": "2022-05",
      "shift": {
        "date": "2022-05-02",
        "shiftId": [
          {
            "_id": "622b55f8f59dcdd1ab9b36b1",
            "date": "2022-05-02",
            "name": "Morning"
          }
        ]
      }
    }
  },
  {
    "_id": "62626a7446ba9a911a623b37",
    "name": "Pinki Das",
    "shifts": {
      "_id": "62636ba4cbda6d2b17f5cae1",
      "month": "2022-05",
      "shift": {
        "date": "2022-05-01",
        "shiftId": [
          {
            "_id": "622bb0f4b88dc92e3c2cac56",
            "date": "2022-05-01",
            "name": "Day"
          }
        ]
      }
    }
  }
]

But my require data should looks like as bellow. shiftId should nested under shift array along with shifts data.

{
        "_id": "621eedae92979fd8f0e9451d",
        "name": "Pallab Koley",
        "shifts": {
            "_id": "62636b9fcbda6d2b17f5cae0",
            "month": "2022-05",
            "shift": [
                {
                    "date": "2022-05-01",
                    "shiftId": [
                        {
                            "_id": "622bb0f4b88dc92e3c2cac56",
                            "date": "2022-05-01",
                            "name": "Day"
                        }
                    ]
                },
                {
                    "date": "2022-05-02",
                    "shiftId": [
                        {
                            "_id": "622b55f8f59dcdd1ab9b36b1",
                            "date": "2022-05-02",
                            "name": "Morning"
                        }
                    ]
                }
            ]
        }
    },
    {
        "_id": "62626a7446ba9a911a623b37",
        "name": "Pinki Das",
        "shifts": {
            "_id": "62636ba4cbda6d2b17f5cae1",
            "month": "2022-05",
            "shift": {
                "date": "2022-05-01",
                "shiftId": [
                    {
                        "_id": "622bb0f4b88dc92e3c2cac56",
                        "date": "2022-05-01",
                        "name": "Day"
                    }
                ]
            }
        }
    }
]

Here is date field under shift is missing. And also need to group the shift array. Please help me out. PlayGround

CodePudding user response：

Use $set after $lookup

db.employees.aggregate([
  {
    $lookup: {
      from: "shifts",
      localField: "shifts.shift.shiftId",
      foreignField: "_id",
      as: "shifts.shift2"
    }
  },
  {
    $set: {
      "shifts.shift": {
        $map: {
          input: "$shifts.shift",
          as: "s",
          in: {
            $mergeObjects: [
              "$$s",
              {
                shiftId: {
                  $filter: {
                    input: "$shifts.shift2",
                    as: "s2",
                    cond: { $eq: [ "$$s2._id", "$$s.shiftId" ] }
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  {
    $unset: [ "shifts.shift2" ]
  }
])

mongoplayground