基于Node.js+MongoDB的夜班考勤数据合并问题求助

需求说明 #

当日班次为夜班（shift.shiftType.isNight: true 或 shiftType.isNight: true）时，将次日的第一条考勤记录合并到当日的attendances数组中，同时移除被提取过记录的次日数据（若次日仅一条记录则直接移除）。

当前数据集 #

[{
        "date": "2023-04-01",
        "shift": {
            "_id": "6422d103994726677e9105c3",
            "date": "2023-04-01",
            "shiftType": {
                "_id": "6299fd7504978a2ba513e0a2",
                "name": "Next Day End",
                "isNight": true
            }
        },
        "attendances": [{
                "_id": "6422d1b9994726677e9105c6",
                "employee": "622061b73b2eaac4b15d42e4",
                "dateTime": "2023-04-01T05:30:00.000Z"
            }
        ]
    },
    {
        "date": "2023-04-02",
        "shift": null,
        "attendances": [{
                "_id": "6422d1b9994726677e9105c7",
                "employee": "622061b73b2eaac4b15d42e4",
                "dateTime": "2023-04-02T00:30:00.000Z"
            },
            {
                "_id": "6423286e2746f65c2480a13e",
                "employee": "622061b73b2eaac4b15d42e4",
                "dateTime": "2023-04-02T04:30:00.000Z"
            },
            {
                "_id": "6423286e2746f65c2480a13f",
                "employee": "622061b73b2eaac4b15d42e4",
                "dateTime": "2023-04-02T12:40:00.000Z"
            }
        ]
    },
    {
        "date": "2023-04-03",
        "shiftType": {
                "_id": "6299fd7504978a2ba513e0a2",
                "name": "General",
                "isNight": false
            },
        "attendances": [{
                "_id": "642328a42746f65c2480a140",
                "employee": "622061b73b2eaac4b15d42e4",
                "dateTime": "2023-04-03T04:05:00.000Z"
            },
            {
                "_id": "642328a42746f65c2480a141",
                "employee": "622061b73b2eaac4b15d42e4",
                "dateTime": "2023-04-03T13:45:00.000Z"
            }
        ]
    },
    {
        "date": "2023-04-04",
        "shift": {
            "_id": "6422d10a994726677e9105c5",
            "date": "2023-04-04",
            "shiftType": {
                "_id": "6299fd7504978a2ba513e0a2",
                "name": "Next Day End",
                "isNight": true
            }
        },
        "attendances": [{
                "_id": "6423292f2746f65c2480a142",
                "employee": "622061b73b2eaac4b15d42e4",
                "dateTime": "2023-04-04T12:30:00.000Z"
            }
        ]
    },
    {
        "date": "2023-04-05",
        "shift": null,
        "attendances": [{
                "_id": "6423292f2746f65c2480a143",
                "employee": "622061b73b2eaac4b15d42e4",
                "dateTime": "2023-04-04T21:55:00.000Z"
            }
        ]
    }
]

期望输出 #

[{
        "date": "2023-04-01",
        "shift": {
            "_id": "6422d103994726677e9105c3",
            "date": "2023-04-01",
            "shiftType": {
                "_id": "6299fd7504978a2ba513e0a2",
                "name": "Next Day End",
                "isNight": true
            }
        },
        "attendances": [{
                "_id": "6422d1b9994726677e9105c6",
                "employee": "622061b73b2eaac4b15d42e4",
                "dateTime": "2023-04-01T05:30:00.000Z"
            },
            {
                "_id": "6422d1b9994726677e9105c7",
                "employee": "622061b73b2eaac4b15d42e4",
                "dateTime": "2023-04-02T00:30:00.000Z"
            }
        ]
    },
    {
        "date": "2023-04-02",
        "shift": null,
        "attendances": [{
                "_id": "6423286e2746f65c2480a13e",
                "employee": "622061b73b2eaac4b15d42e4",
                "dateTime": "2023-04-02T04:30:00.000Z"
            },
            {
                "_id": "6423286e2746f65c2480a13f",
                "employee": "622061b73b2eaac4b15d42e4",
                "dateTime": "2023-04-02T12:40:00.000Z"
            }
        ]
    },
    {
        "date": "2023-04-03",
        "shiftType": {
                "_id": "6299fd7504978a2ba513e0a2",
                "name": "General",
                "isNight": false
            },
        "attendances": [{
                "_id": "642328a42746f65c2480a140",
                "employee": "622061b73b2eaac4b15d42e4",
                "dateTime": "2023-04-03T04:05:00.000Z"
            },
            {
                "_id": "642328a42746f65c2480a141",
                "employee": "622061b73b2eaac4b15d42e4",
                "dateTime": "2023-04-03T13:45:00.000Z"
            }
        ]
    },
    {
        "date": "2023-04-04",
        "shift": {
            "_id": "6422d10a994726677e9105c5",
            "date": "2023-04-04",
            "shiftType": {
                "_id": "6299fd7504978a2ba513e0a2",
                "name": "Next Day End",
                "isNight": true
            }
        },
        "attendances": [{
                "_id": "6423292f2746f65c2480a142",
                "employee": "622061b73b2eaac4b15d42e4",
                "dateTime": "2023-04-04T12:30:00.000Z"
            },
            {
                "_id": "6423292f2746f65c2480a143",
                "employee": "622061b73b2eaac4b15d42e4",
                "dateTime": "2023-04-04T21:55:00.000Z"
            }
        ]
    }
]

MongoDB聚合解决方案 #

使用以下聚合管道实现需求（替换"collection"为你的实际集合名称）：

db.collection.aggregate([
  // 1. 计算次日日期，标记是否为夜班
  {
    $addFields: {
      nextDate: {
        $dateToString: {
          format: "%Y-%m-%d",
          date: { $add: [{ $toDate: "$date" }, 86400000] }
        }
      },
      isNightShift: {
        $or: [
          { $eq: ["$shift.shiftType.isNight", true] },
          { $eq: ["$shiftType.isNight", true] }
        ]
      }
    }
  },
  // 2. 自连接获取次日文档
  {
    $lookup: {
      from: "collection",
      localField: "nextDate",
      foreignField: "date",
      as: "nextDayDoc"
    }
  },
  // 3. 合并夜班文档的次日第一条考勤，判断次日是否有剩余记录
  {
    $addFields: {
      attendances: {
        $cond: {
          if: "$isNightShift",
          then: {
            $concatArrays: [
              "$attendances",
              { $slice: [{ $arrayElemAt: ["$nextDayDoc.attendances", 0] }, 1] }
            ]
          },
          else: "$attendances"
        }
      },
      nextDayHasRemaining: {
        $cond: {
          if: "$isNightShift",
          then: { $gt: [{ $size: { $arrayElemAt: ["$nextDayDoc.attendances", 0] } }, 1] },
          else: false
        }
      },
      nextDayDate: { $arrayElemAt: ["$nextDayDoc.date", 0] }
    }
  },
  // 4. 拆分并处理次日文档（有剩余则保留，否则丢弃）
  {
    $unwind: {
      path: "$nextDayDoc",
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $replaceRoot: {
      newRoot: {
        $cond: [
          { $and: ["$isNightShift", "$nextDayHasRemaining"] },
          { $mergeObjects: ["$nextDayDoc", { attendances: { $slice: ["$nextDayDoc.attendances", 1, { $size: "$nextDayDoc.attendances" }] } }] },
          { $mergeObjects: "$$ROOT", { nextDate: "$$REMOVE", isNightShift: "$$REMOVE", nextDayDoc: "$$REMOVE", nextDayHasRemaining: "$$REMOVE", nextDayDate: "$$REMOVE" } }
        ]
      }
    }
  },
  // 5. 按日期去重，移除已被合并空的次日文档
  {
    $group: {
      _id: "$date",
      doc: { $first: "$$ROOT" }
    }
  },
  // 6. 恢复结构并按日期排序
  {
    $replaceRoot: { newRoot: "$doc" }
  },
  {
    $sort: { date: 1 }
  }
])

步骤说明 #

• 步骤1：计算每个日期对应的次日，同时标记当前文档是否属于夜班班次。
• 步骤2：通过自连接查询获取次日的考勤文档数据。
• 步骤3：对夜班文档，将次日的第一条考勤记录合并到当前文档的考勤数组；同时判断次日文档是否还有剩余的考勤记录需要保留。
• 步骤4-5：拆分文档并处理次日数据，若次日还有剩余考勤则保留（移除已合并的第一条），否则直接丢弃；最后按日期去重，避免重复数据。
• 步骤6：恢复原始文档结构，并按日期升序排序。

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