MongoDB : - Merge Two Array with same key

Question

I have 3 collections, users, attendances, shifts. I am trying to get attendances and shifts with the help of users I have a date in shifts and the Date key in attendances and I am trying to merge both data into 1 as per date, and if not present then that key should be none

Below is the query I tried it is working to some extent but not got the final result

User.aggregate([
  { $sort: { workerId: 1 } },
  {
    $lookup: {
      from: "shifts",
      localField: "_id",
      foreignField: "employeeId",
      pipeline: [
        {
          $match: {
            date: {
              $gte: new Date(fromDate),
              $lte: new Date(toDate),
            },
          },
        },
        {
          $project: {
            date: 1,
            shiftCode: 1,
          },
        },
        {
          $sort: {
            date: 1,
          },
        },
      ],
      as: "shifts",
    },
  },
  {
    $project: {
      _id: 1,
      workerId: 1,
      shiftListData: "$shifts",
    },
  },
  {
    $lookup: {
      from: "attendances",
      localField: "_id",
      foreignField: "employeeId",
      pipeline: [
        {
          $match: {
            Date: { $gte: new Date(fromDate), $lte: new Date(toDate) },
          },
        },
        {
          $project: {
            inTime: 1,
            name: 1,
            Date: 1,
          },
        },
      ],
      as: "attendances",
    },
  },
]);

OutPut

[
  {
    "workerId": "1005",
    "shiftListData": [
      {
        "_id": "63875e8182ebbe13ee9531d4",
        "shiftCode": "HOBGS_1100",
        "date": "2022-12-31T00:00:00.000Z"
      },
      {
        "_id": "63b277a2f6a8eccb2d95d407",
        "shiftCode": "WO",
        "date": "2023-01-01T00:00:00.000Z"
      },
      {
        "_id": "63b27787f6a8eccb2d95cf30",
        "shiftCode": "HOBGS_1100",
        "date": "2023-01-02T00:00:00.000Z"
      },
      {
        "_id": "63b277a2f6a8eccb2d95d409",
        "shiftCode": "HOBGS_1100",
        "date": "2023-01-03T00:00:00.000Z"
      }
    ],
    "attendances": [
      {
        "_id": "61307cd385b5055a15cec159",
        "Date": "2022-12-31T00:00:00.000Z",
        "inTime": "2022-12-31T11:16:10.000Z",
        "name": "name2"
      },
      {
        "_id": "63b236ef3980cffaf7715d62",
        "inTime": "2023-01-02T07:14:08.000Z",
        "Date": "2023-01-02T00:00:00.000Z",
        "name": "name2"
      }
    ]
  },
  {
    "workerId": "1006",
    "shiftListData": [
      {
        "_id": "63875e8182ebbe13ee9531d2",
        "shiftCode": "HOBGS_1100",
        "date": "2022-12-31T00:00:00.000Z"
      },
      {
        "_id": "63b277a2f6a8eccb2d95d403",
        "shiftCode": "WO",
        "date": "2023-01-01T00:00:00.000Z"
      },
      {
        "_id": "63b27787f6a8eccb2d95cf39",
        "shiftCode": "HOBGS_1100",
        "date": "2023-01-02T00:00:00.000Z"
      },
      {
        "_id": "63b277a2f6a8eccb2d95d400",
        "shiftCode": "HOBGS_1100",
        "date": "2023-01-03T00:00:00.000Z"
      }
    ],
    "attendances": [
      {
        "_id": "61307cd385b5055a15cec158",
        "Date": "2022-12-31T00:00:00.000Z",
        "inTime": "2022-12-31T11:16:10.000Z",
        "name": "name"
      },
      {
        "_id": "63b236ef3980cffaf7715d69",
        "inTime": "2023-01-02T07:14:08.000Z",
        "Date": "2023-01-02T00:00:00.000Z",
        "name": "name"
      }
    ]
  }
]

I want to merge shiftListData and attendances as per date into one array

Example : -

[
  {
    "workerId": "1005",
    "newData": [
      {
        "_id": "63875e8182ebbe13ee9531d4",
        "shiftCode": "HOBGS_1100",
        "date": "2022-12-31T00:00:00.000Z",
        "attendanceId": "61307cd385b5055a15cec159",
        "attendanceDate": "2022-12-31T00:00:00.000Z",
        "inTime": "2022-12-31T11:16:10.000Z",
        "name": "name2"
      },
      {
        "_id": "63b277a2f6a8eccb2d95d407",
        "shiftCode": "WO",
        "date": "2023-01-01T00:00:00.000Z"
      },
      {
        "_id": "63b27787f6a8eccb2d95cf30",
        "shiftCode": "HOBGS_1100",
        "date": "2023-01-02T00:00:00.000Z",
        "attendanceId": "63b236ef3980cffaf7715d62",
        "inTime": "2023-01-02T07:14:08.000Z",
        "attendanceDate": "2023-01-02T00:00:00.000Z",
        "name": "name2"
      },
      {
        "_id": "63b277a2f6a8eccb2d95d409",
        "shiftCode": "HOBGS_1100",
        "date": "2023-01-03T00:00:00.000Z"
      }
    ]
  },
  {
    "workerId": "1006",
    "newData": [
      {
        "_id": "63875e8182ebbe13ee9531d2",
        "shiftCode": "HOBGS_1100",
        "date": "2022-12-31T00:00:00.000Z",
        "attendanceId": "61307cd385b5055a15cec158",
        "attendanceDate": "2022-12-31T00:00:00.000Z",
        "inTime": "2022-12-31T11:16:10.000Z",
        "name": "name"
      },
      {
        "_id": "63b277a2f6a8eccb2d95d403",
        "shiftCode": "WO",
        "date": "2023-01-01T00:00:00.000Z"
      },
      {
        "_id": "63b27787f6a8eccb2d95cf39",
        "shiftCode": "HOBGS_1100",
        "date": "2023-01-02T00:00:00.000Z",
        "attendanceId": "63b236ef3980cffaf7715d69",
        "inTime": "2023-01-02T07:14:08.000Z",
        "attendanceDate": "2023-01-02T00:00:00.000Z",
        "name": "name"
      },
      {
        "_id": "63b277a2f6a8eccb2d95d400",
        "shiftCode": "HOBGS_1100",
        "date": "2023-01-03T00:00:00.000Z"
      }
    ]
  }
]

nimrod serok · Accepted Answer · 2023-01-03 16:16:22Z

1

One option is to add one more step to your pipeline which finds for each shift item its matching item in attendances using $filter:

  {$set: {
      shiftListData: {$map: {
          input: "$shiftListData",
          as: "shift",
          in: {$mergeObjects: [
              "$$shift",
              {$ifNull: [
                  {$first: {
                      $filter: {
                        input: "$attendances",
                        cond: {$eq: ["$$this.Date", "$$shift.date"]}
                      }
                  }},
                  {}
              ]}
          ]}
      }},
      attendances: "$$REMOVE"
  }}

See how it works on the playground example

answered Jan 3, 2023 at 16:16

nimrod serok

16.1k2 gold badges15 silver badges36 bronze badges

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

3 Comments

chirag prajapati Over a year ago

Thanks, it is working, you are an amazing man can I get your mail id or cell number to connect in the future if I need help?

chirag prajapati Over a year ago

Everything is working but there is one more thing in this if array1 i.e shiftListData does not have any data of any date, and array2 i.e attendances have data of date then it will not merge it and return nothing like it avoids that object. how do i stop this?

nimrod serok Over a year ago

You can solve this with $cond, like this

Collectives™ on Stack Overflow

MongoDB : - Merge Two Array with same key

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related