如何使用mongoDB在每个嵌套阵列上平均间隔

Question

我们正在使用数据尺寸的尺寸存储量图案，我们想知道如何在此嵌套数组上汇总每1、5或10分钟的平均值。结构如下：

{
    "_id" : ObjectId("62ad1548a7d67adf4943d2f7"),
    "data" : ObjectId("6262a3048cbdacd24c38fbc4"),
    "day" : ISODate("2022-06-17T00:00:00.000Z"),
    "first" : ISODate("2022-06-17T23:59:04.659Z"),
    "last" : ISODate("2022-06-17T23:59:59.915Z"),
    "nSamples" : 97,
    "samples" : [ 
        {
            "time" : ISODate("2022-06-17T23:59:04.659Z"),
            "value" : 8
        }, 
        {
            "time" : ISODate("2022-06-17T23:59:50.706Z"),
            "value" : 6
        }, 
           ///// MAX 200 Documents in nested array
        {
            "time" : ISODate("2022-06-17T23:59:59.342Z"),
            "value" : 4
        }, 
        {
            "time" : ISODate("2022-06-17T23:59:59.915Z"),
            "value" : 12
        }
    ],
    "version" : 0
}
{
    "_id" : ObjectId("62ad1548a7d67adf4943d2f7"),
    "data" : ObjectId("62629d178cbdac1e9938fb9e"),
    "day" : ISODate("2022-06-17T00:00:00.000Z"),
    "first" : ISODate("2022-06-17T23:32:04.659Z"),
    "last" : ISODate("2022-06-17T23:40:59.915Z"),
    "nSamples" : 97,
    "samples" : [ 
        {
            "time" : ISODate("2022-06-17T23:32:04.659Z"),
            "value" : 2
        }, 
        {
            "time" : ISODate("2022-06-17T23:33:50.706Z"),
            "value" : 65
        }, 
           ///// MAX 200 Documents in nested array
        {
            "time" : ISODate("2022-06-17T23:36:59.342Z"),
            "value" : 45
        }, 
        {
            "time" : ISODate("2022-06-17T23:40:59.915Z"),
            "value" : 22
        }
    ],
    "version" : 0
}

有可能获得这种结构吗？

{
    "_id" : ObjectId("62ad1548a7d67adf4943d2f7"),
    "data" : ObjectId("6262a3048cbdacd24c38fbc4"),
    "day" : ISODate("2022-06-17T00:00:00.000Z"),
    "first" : ISODate("2022-06-17T23:59:04.659Z"),
    "last" : ISODate("2022-06-17T23:59:59.915Z"),
    "nSamples" : 97,
    "samples" : [ 
        // Less documents
        {
            "time" : ISODate("2022-06-17T23:56:00.000Z"),
            "value" : 7
        }, 
        {
            "time" : ISODate("2022-06-17T23:58:00.000Z"),
            "value" : 6
        }, 
        {
            "time" : ISODate("2022-06-17T23:59:00.000Z"),
            "value" : 4
        }, 
    ],
    "version" : 0
}
{
    "_id" : ObjectId("62ad1548a7d67adf4943d2f7"),
    "data" : ObjectId("62629d178cbdac1e9938fb9e"),
    "day" : ISODate("2022-06-17T00:00:00.000Z"),
    "first" : ISODate("2022-06-17T23:59:04.659Z"),
    "last" : ISODate("2022-06-17T23:59:59.915Z"),
    "nSamples" : 97,
    "samples" : [ 
// Less docs
        {
            "time" : ISODate("2022-06-17T23:32:00.000Z"),
            "value" : 2
        }, 
        {
            "time" : ISODate("2022-06-17T23:33:00.000Z"),
            "value" : 65
        }, 
        {
            "time" : ISODate("2022-06-17T23:36:00.000Z"),
            "value" : 45
        }, 
        {
            "time" : ISODate("2022-06-17T23:37:00.000Z"),
            "value" : 22
        }
    ],
    "version" : 0
}

到目前为止，我首先是从$ match选择值开始的，然后添加一个“ fromDate”值以进行搜索：

$match: {
  $and: [
    {
      data: {"$in": [ObjectId("62629d178cbdac1e9938fb9e"), ObjectId("62629d178cbdac1e9938fb9f"), ObjectId("62629d178cbdac1e9938fb9d")]} ,
    },
    {
      first: {
        $gte: new Date('2022-07-08T08:10:22.525+00:00')
      }
    }
  ],
}

然后使用$ undind在样本上汇总

$unwind: {
  path: "$samples",
  preserveNullAndEmptyArrays: true
}

，然后分组有一个间隔的值：

$group: {
  _id: {
    "$toDate": {
        "$subtract": [
          { "$toLong": "$samples.time" },
          { "$mod": [ { "$toLong": "$samples.time" }, 1000 * 60 * 1 ] } // 1 for 1 minute could be replaced by 5, 10 etc...
        ]
      }
  },
  values: {
    $avg: "$samples.value"
  }
}

这里的问题是，考虑到我有多个“数据” ...我如何也可以按数据进行分组？是否可以保持相同的结构并拥有“较少的根文档”？

Answer 1

继续查询的一个选项是：

db.collection.aggregate([
  {$match: {
      data: {"$in": [
          ObjectId("62629d178cbdac1e9938fb9e"),
          ObjectId("6262a3048cbdacd24c38fbc4"),
          ObjectId("62629d178cbdac1e9938fb9d")
        ]
      },
      first: {$gte: ISODate("2021-07-08T08:10:22.525+00:00")}
    }
  },
  {$unwind: {path: "$samples", preserveNullAndEmptyArrays: true}},
  {$group: {
      _id: {
        time: {$toDate: {
            $subtract: [
              {$toLong: "$samples.time"},
              {$mod: [{$toLong: "$samples.time"}, 60000]}
            ]
          }
        },
        orig_id: "$_id"
      },
      values: {$avg: "$samples.value"},
      root: {$first: "$ROOT"}
    }
  },
  {$sort: {"_id.time": 1}},
  {$group: {_id: "$_id.orig_id",  root: {$first: "$root"},
      samples: {$push: {time: "$_id.time", value: "$values"}}}
  },
  {$set: {"root.samples": "$samples", "root._id": "$_id"}},
  {$replaceRoot: {newRoot: "$root"}}
])

查看如何在 Playground示例