MongoDB聚合计算子文档日期差 筛选间隔超1小时的记录

问题背景 #

现有如下结构的MongoDB集合文档：

[
    {
        "CDF": {
            "UTILITYTYPE": {
                "D1": {
                    "G1": "12387835",
                    "G22": {
                        "NAME": "L1"
                    }
                },
                "D5": {
                    "EVENT": [
                        {
                            "CODE": "13",
                            "TIME": "29-05-2022 13:26:00",
                            "STATUS": "0"
                        },
                        {
                            "CODE": "13",
                            "TIME": "29-05-2022 14:41:00",
                            "STATUS": "1"
                        },
                        {
                            "CODE": "13",
                            "TIME": "31-05-2022 10:13:00",
                            "STATUS": "0"
                        },
                        {
                            "CODE": "13",
                            "TIME": "31-05-2022 10:18:00",
                            "STATUS": "1"
                        }
                    ]
                }
            }
        }
    },
    {
        "CDF": {
            "UTILITYTYPE": {
                "D1": {
                    "G1": "12388215",
                    "G22": {
                        "NAME": "L2"
                    }
                },
                "D5": {
                    "EVENT": [
                        {
                            "CODE": "7",
                            "TIME": "16-05-2022 04:28:21",
                            "STATUS": "0"
                        },
                        {
                            "CODE": "7",
                            "TIME": "16-05-2022 06:30:30",
                            "STATUS": "1"
                        },
                        {
                            "CODE": "7",
                            "TIME": "16-05-2022 07:36:53",
                            "STATUS": "0"
                        },
                        {
                            "CODE": "7",
                            "TIME": "16-05-2022 19:39:28",
                            "STATUS": "1"
                        }
                    ]
                }
            }
        }
    }
]

需要实现3个核心需求：

• 将EVENT数组中dd-MM-yyyy HH:mm:ss格式的字符串TIME字段转换为可比较的标准日期类型
• 按数组顺序两两配对（第1条配第2条、第3条配第4条，以此类推）计算每对事件的时间差
• 筛选出存在配对事件时间差超过1小时的文档，返回对应文档的CDF.UTILITYTYPE.D1.G1字段值，以及符合时间差要求的事件对。

示例规则：第一条文档中CODE为13的前两个事件时间差为75分钟，超过1小时，需要返回G1值12387835以及对应的两个事件对象。

实现聚合语句 #

以下聚合管道适配MongoDB 4.2+版本，替换代码中的集合名即可直接执行：

db.你的实际集合名.aggregate([
  // 转换EVENT数组内的时间字符串为日期类型，追加索引字段用于后续配对
  {
    $addFields: {
      "CDF.UTILITYTYPE.D5.EVENT": {
        $map: {
          input: { $range: [0, { $size: "$CDF.UTILITYTYPE.D5.EVENT" }] },
          as: "idx",
          in: {
            $mergeObjects: [
              { $arrayElemAt: ["$CDF.UTILITYTYPE.D5.EVENT", "$$idx"] },
              {
                index: "$$idx",
                eventDate: {
                  $dateFromString: {
                    dateString: { $arrayElemAt: ["$CDF.UTILITYTYPE.D5.EVENT.TIME", "$$idx"] },
                    format: "%d-%m-%Y %H:%M:%S"
                  }
                }
              }
            ]
          }
        }
      }
    }
  },
  // 按偶数索引拆分，两两配对生成事件对并计算时间差
  {
    $addFields: {
      eventPairs: {
        $map: {
          input: {
            $filter: {
              input: { $range: [0, { $size: "$CDF.UTILITYTYPE.D5.EVENT" }] },
              cond: { $eq: [{ $mod: ["$$this", 2] }, 0] }
            }
          },
          as: "startIdx",
          in: {
            startEvent: { $arrayElemAt: ["$CDF.UTILITYTYPE.D5.EVENT", "$$startIdx"] },
            endEvent: { $arrayElemAt: ["$CDF.UTILITYTYPE.D5.EVENT", { $add: ["$$startIdx", 1] }] },
            durationMs: {
              $subtract: [
                { $arrayElemAt: ["$CDF.UTILITYTYPE.D5.EVENT.eventDate", { $add: ["$$startIdx", 1] }] },
                { $arrayElemAt: ["$CDF.UTILITYTYPE.D5.EVENT.eventDate", "$$startIdx"] }
              ]
            }
          }
        }
      }
    }
  },
  // 过滤出时间差超过1小时（3600*1000毫秒）的事件对
  {
    $addFields: {
      matchedPairs: {
        $filter: {
          input: "$eventPairs",
          cond: { $gt: ["$$this.durationMs", 3600000] }
        }
      }
    }
  },
  // 剔除没有符合条件事件对的文档
  {
    $match: {
      "matchedPairs.0": { $exists: true }
    }
  },
  // 格式化输出，移除临时字段，还原要求的事件结构
  {
    $project: {
      _id: 0,
      G1: "$CDF.UTILITYTYPE.D1.G1",
      validEventPairs: {
        $map: {
          input: "$matchedPairs",
          as: "pair",
          in: [
            {
              CODE: "$$pair.startEvent.CODE",
              TIME: "$$pair.startEvent.TIME",
              STATUS: "$$pair.startEvent.STATUS"
            },
            {
              CODE: "$$pair.endEvent.CODE",
              TIME: "$$pair.endEvent.TIME",
              STATUS: "$$pair.endEvent.STATUS"
            }
          ]
        }
      }
    }
  }
])

逻辑说明 #

• 时间解析通过$dateFromString指定匹配日-月-年 时:分:秒格式，避免跨地域日期解析错误
• 配对逻辑通过数组索引取模2等于0筛选起点，自动适配任意偶数长度的EVENT数组，不会出现索引越界
• 日期类型直接做减法得到毫秒级时间差，1小时阈值固定为3600000毫秒，判断精度准确
• 最终输出的事件对完全保留原始字符串格式的TIME字段，和需求示例格式完全一致

执行后返回结果结构如下：

[
  {
    "G1": "12387835",
    "validEventPairs": [
      [
        {
          "CODE": "13",
          "TIME": "29-05-2022 13:26:00",
          "STATUS": "0"
        },
        {
          "CODE": "13",
          "TIME": "29-05-2022 14:41:00",
          "STATUS": "1"
        }
      ]
    ]
  },
  {
    "G1": "12388215",
    "validEventPairs": [
      [
        {
          "CODE": "7",
          "TIME": "16-05-2022 04:28:21",
          "STATUS": "0"
        },
        {
          "CODE": "7",
          "TIME": "16-05-2022 06:30:30",
          "STATUS": "1"
        }
      ],
      [
        {
          "CODE": "7",
          "TIME": "16-05-2022 07:36:53",
          "STATUS": "0"
        },
        {
          "CODE": "7",
          "TIME": "16-05-2022 19:39:28",
          "STATUS": "1"
        }
      ]
    ]
  }
]

内容的提问来源于stack exchange，提问作者Aksheet-Goku