MongoDB按时间范围匹配后计算value与time的最值及平均值实现

首先明确：$match 是用来过滤整个文档的操作符，筛选数组内的指定元素需要使用 $filter 操作符实现，完整聚合方案如下： #

前置示例结构说明 #

输入文档结构 #

{
  "_id": ObjectId("xxx"),
  "data": {
    "time_start": ISODate("2024-01-01T00:00:00Z"),
    "time_end": ISODate("2024-01-03T00:00:00Z")
  },
  "time_values": [
    {"time": ISODate("2023-12-31T12:00:00Z"), "value": 10},
    {"time": ISODate("2024-01-01T12:00:00Z"), "value": 20},
    {"time": ISODate("2024-01-02T12:00:00Z"), "value": 30},
    {"time": ISODate("2024-01-04T12:00:00Z"), "value": 40}
  ]
}

完整聚合管道代码 #

db.collection.aggregate([
  // 第一步：筛选符合时间范围的数组元素，生成remaining_time_values字段
  {
    $addFields: {
      remaining_time_values: {
        $filter: {
          input: "$time_values",
          cond: {
            $and: [
              {$gte: ["$$this.time", "$data.time_start"]},
              {$lte: ["$$this.time", "$data.time_end"]}
            ]
          }
        }
      }
    }
  },
  // 第二步：计算统计值并写入data字段
  {
    $addFields: {
      data: {
        // 保留原有data字段的内容
        $mergeObjects: [
          "$data",
          // 新增统计字段
          {
            // 计算value平均值
            value_avg: {$avg: "$remaining_time_values.value"},
            // 计算time平均值（时间会转为毫秒数计算后再转回日期格式）
            time_avg: {$toDate: {$avg: {$map: {input: "$remaining_time_values", in: {$toLong: "$$this.time"}}}}},
            // 最小value值
            value_min: {$min: "$remaining_time_values.value"},
            // 最小value对应的time
            time_min: {
              $getField: {
                input: {$minN: {input: "$remaining_time_values", n: 1, sortBy: {value: 1}}},
                field: "time"
              }
            },
            // 最大value值
            value_max: {$max: "$remaining_time_values.value"},
            // 最大value对应的time
            time_max: {
              $getField: {
                input: {$maxN: {input: "$remaining_time_values", n: 1, sortBy: {value: 1}}},
                field: "time"
              }
            }
          }
        ]
      }
    }
  }
  // 可选：如果需要删除原time_values字段，加下面的$project阶段
  // {$project: {time_values: 0}}
])

低版本MongoDB兼容方案 #

如果你使用的MongoDB版本低于5.2（不支持$minN/$maxN），可以用$reduce实现极值对应的时间查询，以time_min为例：

time_min: {
  $reduce: {
    input: "$remaining_time_values",
    initialValue: null,
    in: {
      $cond: [
        // 初始值为空直接取当前元素的时间
        {$eq: ["$$value", null]},
        "$$this.time",
        // 当前元素value更小则替换时间
        {$cond: [{$lt: ["$$this.value", "$$value.value"]}, "$$this.time", "$$value"]}
      ]
    }
  }
}

输出结构示例 #

{
  "_id": ObjectId("xxx"),
  "data": {
    "time_start": ISODate("2024-01-01T00:00:00Z"),
    "time_end": ISODate("2024-01-03T00:00:00Z"),
    "value_avg": 25,
    "time_avg": ISODate("2024-01-02T00:00:00Z"),
    "value_min": 20,
    "time_min": ISODate("2024-01-01T12:00:00Z"),
    "value_max": 30,
    "time_max": ISODate("2024-01-02T12:00:00Z")
  },
  "time_values": [/* 原有数组保留，不需要可通过$project删除 */],
  "remaining_time_values": [
    {"time": ISODate("2024-01-01T12:00:00Z"), "value": 20},
    {"time": ISODate("2024-01-02T12:00:00Z"), "value": 30}
  ]
}

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