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

CodePudding user response：

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