arrays - 匹配文档中的时间和值，这将在 MongoDB 中从该文档中找到 min、avg、max

Question

解释：我想找到value_avg, time_avg, value_min, time_min, value_max, time_max. 在time_values我们有对象time和value字段时，首先我们必须$match使用time_valueswithdata.time_start和data.time_end。第二步可能在时间范围之间$match，我们会找到value_avg, time_avg, value_min, time_min, value_max, time_max. 请查看预期输出。在预期输出remaining_time_values中将那些元素$match。如果您有任何问题，请告诉我。提前致谢。

{
  "data": {
    "time_start": "2021-09-09T09:17:01.891Z",
    "time_end": "2021-09-09T09:17:11.091Z",
    "value_start": 142,
    "value_end": 1384
  },
  "time_values": [
    {
      "time": "2021-09-09T09:17:01.491Z",
      "value": 0
    },
    {
      "time": "2021-09-09T09:17:01.691Z",
      "value": 10
    },
    {
      "time": "2021-09-09T09:17:01.891Z",
      "value": 142
    },
    {
      "time": "2021-09-09T09:17:02.091Z",
      "value": 479
    },
    {
      "time": "2021-09-09T09:17:02.291Z",
      "value": 1166
    },
    {
      "time": "2021-09-09T09:17:02.491Z",
      "value": 1430
    },
    {
      "time": "2021-09-09T09:17:02.691Z",
      "value": 1089
    },
    {
      "time": "2021-09-09T09:17:02.891Z",
      "value": 759
    },
    {
      "time": "2021-09-09T09:17:03.091Z",
      "value": 896
    },
    {
      "time": "2021-09-09T09:17:03.291Z",
      "value": 1331
    },
    {
      "time": "2021-09-09T09:17:03.491Z",
      "value": 1384
    },
    {
      "time": "2021-09-09T09:17:11.091Z",
      "value": 1384
    },
    {
      "time": "2021-09-09T09:17:11.291Z",
      "value": 0
    },
    {
      "time": "2021-09-09T09:17:21.095Z",
      "value": 0
    },
    {
      "time": "2021-09-09T09:17:21.300Z",
      "value": 0
    },
    {
      "time": "2021-09-09T09:17:23.695Z",
      "value": 0
    },
    {
      "time": "2021-09-09T09:17:23.895Z",
      "value": 270
    },
    {
      "time": "2021-09-09T09:17:24.095Z",
      "value": 492
    },
    {
      "time": "2021-09-09T09:17:24.295Z",
      "value": 603
    },
    {
      "time": "2021-09-09T09:17:24.495Z",
      "value": 769
    },
    {
      "time": "2021-09-09T09:17:24.701Z",
      "value": 851
    },
    {
      "time": "2021-09-09T09:17:24.895Z",
      "value": 938
    },
    {
      "time": "2021-09-09T09:17:25.095Z",
      "value": 1120
    },
    {
      "time": "2021-09-09T09:17:25.299Z",
      "value": 1123
    },
    {
      "time": "2021-09-09T09:17:25.495Z",
      "value": 880
    },
    {
      "time": "2021-09-09T09:17:25.695Z",
      "value": 640
    },
    {
      "time": "2021-09-09T09:17:25.895Z",
      "value": 630
    },
    {
      "time": "2021-09-09T09:17:26.095Z",
      "value": 645
    },
    {
      "time": "2021-09-09T09:17:35.896Z",
      "value": 645
    },
    {
      "time": "2021-09-09T09:17:36.106Z",
      "value": 0
    },
    {
      "time": "2021-09-09T09:17:49.097Z",
      "value": 0
    },
    {
      "time": "2021-09-09T09:17:49.295Z",
      "value": 464
    },
    {
      "time": "2021-09-09T09:17:49.495Z",
      "value": 1154
    },
    {
      "time": "2021-09-09T09:17:49.695Z",
      "value": 1548
    },
    {
      "time": "2021-09-09T09:17:49.895Z",
      "value": 1479
    },
    {
      "time": "2021-09-09T09:17:50.095Z",
      "value": 1562
    },
    {
      "time": "2021-09-09T09:17:50.295Z",
      "value": 1731
    },
    {
      "time": "2021-09-09T09:17:50.496Z",
      "value": 1897
    },
    {
      "time": "2021-09-09T09:17:50.695Z",
      "value": 1976
    },
    {
      "time": "2021-09-09T09:17:50.895Z",
      "value": 1922
    },
    {
      "time": "2021-09-09T09:17:51.095Z",
      "value": 1721
    },
    {
      "time": "2021-09-09T09:17:51.296Z",
      "value": 1336
    },
    {
      "time": "2021-09-09T09:17:51.525Z",
      "value": 951
    },
    {
      "time": "2021-09-09T09:17:51.695Z",
      "value": 772
    },
    {
      "time": "2021-09-09T09:17:51.895Z",
      "value": 1008
    },
    {
      "time": "2021-09-09T09:17:52.095Z",
      "value": 1417
    },
    {
      "time": "2021-09-09T09:17:59.095Z",
      "value": 1417
    }
  ]
}

预期的输出文档。

{
"data": {
  "time_start": "2021-09-09T09:17:01.891Z",
  "time_end": "2021-09-09T09:17:11.091Z",
  "value_start": 142,
  "value_end": 1384,
  "value_avg" :"??",
  "time_min" : "??",
  "value_min" : "??",
  "time_max" : "??",
  "value_max" : "??"
},
  "remaining_time_values": [
    {
      "time": "2021-09-09T09:17:01.891Z",
      "value": 142
    },
    {
      "time": "2021-09-09T09:17:02.091Z",
      "value": 479
    },
    {
      "time": "2021-09-09T09:17:02.291Z",
      "value": 1166
    },
    {
      "time": "2021-09-09T09:17:02.491Z",
      "value": 1430
    },
    {
      "time": "2021-09-09T09:17:02.691Z",
      "value": 1089
    },
    {
      "time": "2021-09-09T09:17:02.891Z",
      "value": 759
    },
    {
      "time": "2021-09-09T09:17:03.091Z",
      "value": 896
    },
    {
      "time": "2021-09-09T09:17:03.291Z",
      "value": 1331
    },
    {
      "time": "2021-09-09T09:17:03.491Z",
      "value": 1384
    },
    {
      "time": "2021-09-09T09:17:11.091Z",
      "value": 1384
    }
  ]
}

标签： arraysmongodbmongodb-queryaggregation-framework

Answer 1

您可以使用这样的聚合查询：

• 首先$unwind解构数组并对每个值进行操作。
• 我曾经$set将字符串解析为日期，但如果您的数据仍然是日期对象，则不需要此步骤。
• 然后$match在所需范围之间进行比较（我认为这是您想要的匹配，但如果您想使用$gtor$lt代替，$gte您$lte可以更改它。
• 并$group使用 , 等累加运算符重建数组$avg，$max并$min获得所需的值。

db.collection.aggregate([
  {
    "$unwind": "$time_values"
  },
  {
    "$set": {
      "data.time_start": {
        "$toDate": "$data.time_start"
      },
      "data.time_end": {
        "$toDate": "$data.time_end"
      },
      "time_values.time": {
        "$toDate": "$time_values.time"
      }
    }
  },
  {
    "$match": {
      "$expr": {
        "$and": [
          {
            "$lte": [
              "$data.time_start",
              "$time_values.time"
            ]
          },
          {
            "$gte": [
              "$data.time_end",
              "$time_values.time"
            ]
          }
        ]
      }
    }
  },
  {
    "$group": {
      "_id": "$_id",
      "time_start": {
        "$first": "$data.time_start"
      },
      "time_end": {
        "$first": "$data.time_end"
      },
      "value_start": {
        "$first": "$data.value_start"
      },
      "value_end": {
        "$first": "$data.value_end"
      },
      "remaining_time_values": {
        "$push": "$time_values"
      },
      "value_avg": {
        "$avg": "$time_values.value"
      },
      "time_min": {
        "$min": "$time_values.time"
      },
      "value_min": {
        "$min": "$time_values.value"
      },
      "time_max": {
        "$max": "$time_values.time"
      },
      "value_max": {
        "$max": "$time_values.value"
      }
    }
  }
])

这里的例子