MongoDB lookup in multi label nested array

Question

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

ray · Accepted Answer · 2022-04-24 14:42:01Z

1

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.

answered Apr 24, 2022 at 14:42

ray

15.6k12 gold badges97 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pallab · Accepted Answer · 2022-04-25 01:48:22Z

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

Collectives™ on Stack Overflow

MongoDB lookup in multi label nested array

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related