skip to Main Content

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

2

Answers


  1. Chosen as BEST ANSWER

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

  2. 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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search