MongoDB按日期合并shifts与attendances数组问题求助

我有三个MongoDB集合：users、attendances、shifts，需要通过users集合关联获取另外两个集合的数据。shifts集合有date字段，attendances集合有Date字段，希望按日期将这两类数据合并为一个数组，某类数据不存在时对应字段为null。

现有查询语句 #

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",
    },
  },
]);

当前输出 #

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

期望输出 #

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

解决方案 #

在现有聚合管道后添加以下阶段，即可实现按日期合并两类数据：

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: "shiftListData",
    },
  },
  {
    $lookup: {
      from: "attendances",
      localField: "_id",
      foreignField: "employeeId",
      pipeline: [
        {
          $match: {
            Date: { $gte: new Date(fromDate), $lte: new Date(toDate) },
          },
        },
        {
          $project: {
            attendanceId: "$_id",
            attendanceDate: "$Date",
            inTime: 1,
            name: 1,
            _id: 0,
          },
        },
      ],
      as: "attendances",
    },
  },
  // 转换考勤数据为日期映射对象，方便匹配
  {
    $addFields: {
      attendanceMap: {
        $arrayToObject: {
          $map: {
            input: "$attendances",
            as: "item",
            in: {
              k: { $dateToString: { format: "%Y-%m-%dT%H:%M:%S.%LZ", date: "$$item.attendanceDate" } },
              v: "$$item"
            }
          }
        }
      }
    }
  },
  // 遍历班次数据，合并对应考勤信息
  {
    $addFields: {
      newData: {
        $map: {
          input: "$shiftListData",
          as: "shift",
          in: {
            $mergeObjects: [
              "$$shift",
              { $ifNull: [ { $arrayElemAt: [ { $filter: { input: "$attendances", cond: { $eq: [ "$$this.attendanceDate", "$$shift.date" ] } } }, 0 ] }, {} ] }
            ]
          }
        }
      }
    }
  },
  // 保留需要的输出字段
  {
    $project: {
      workerId: 1,
      newData: 1,
      _id: 0
    }
  }
]);

代码说明 #

1. 字段重命名：在考勤数据的$project阶段，将_id改为attendanceId、Date改为attendanceDate，避免与班次字段冲突。
2. 考勤映射：通过$arrayToObject将考勤数组转为以日期字符串为键的对象，快速匹配对应日期的考勤数据。
3. 数据合并：用$map遍历班次数据，通过$filter匹配同日期的考勤信息，再用$mergeObjects合并两类数据；无考勤数据时用空对象填充，对应字段自动为null或不显示。

内容的提问来源于stack exchange，提问作者chirag prajapati