MongoDB lookup in multi label nested array-CodePudding

I am very new in MongoDB. I am trying to make lookup in multi label nested array. My data is looks like bellow.

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

And I am trying to run lookup like bellow.

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

I am getting the result.

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

But my expectation data should looks like bellow.

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

Here is date field under shifts.shift is missing. shiftId is replacing all the field under shift array. Please help me out. mongoplayground

CodePudding user response：

You are overriding the original content of the array with your lookup result. Consider using subpipeline to store the date as variable and assign it to the lookup result.

db.employees.aggregate([
  {
    "$unwind": "$shifts.shift"
  },
  {
    "$lookup": {
      "from": "shifts",
      "let": {
        shiftDate: "$shifts.shift.date",
        sid: "$shifts.shift.shiftId"
      },
      "pipeline": [
        {
          "$match": {
            $expr: {
              "$eq": [
                "$_id",
                "$$sid"
              ]
            }
          }
        },
        {
          "$addFields": {
            "date": "$$shiftDate"
          }
        }
      ],
      "as": "shifts.shift.shiftId"
    }
  }
])

Here is the Mongo playground for your reference.

CodePudding user response：

Sorry I made a mistake to show my requirement. I have changed the play ground with multiple shift. Modified play ground . And it result should looks like bellow.

    {
        "_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"
                    }
                ]
            }
        }
    }
]