java - 如何使用聚合合并两个或多个包含时间序列数据数组的文档？

Question

我想合并两个或多个文档，以便这些选定文档中的时间序列数据应合并为一个，以便如果该数据在该时间戳不存在，则该数据的数据应为空，例如

以下是文档(可以有多个文档):-

文档 1:-

{
  "unit_id":38,
  "mac_id":"abcdefghijkl",
  "data":{
          "ch1":[21,22,31,31,22,11],
          "ch2":[55,55,56,57,88,90]
  },
  "updatetime":["2019-07-15 17:20:16",
"2019-07-15 17:20:23",
"2019-07-15 17:21:23",
"2019-07-15 17:22:23",
"2019-07-15 17:23:25",
"2019-07-15 17:24:25"]
}

文档2:-

{
  "unit_id":39,
  "mac_id":"abcdefgavdca",
  "data":{
          "ch1":[43.21,55.12,44.21,66.21,11.21,22.10]

  },
  "updatetime":["2019-07-15 17:25:00",
"2019-07-15 17:26:23",
"2019-07-15 17:27:23",
"2019-07-15 17:28:23",
"2019-07-15 17:29:25",
"2019-07-15 17:30:25"]
}

文档 3:-

{
  "unit_id":40,
  "mac_id":"abcdefgzxcvs",
  "data":{
          "ch1":[21,22,31,31,22,11],

  },
  "updatetime":["2019-07-15 17:35:16",
"2019-07-15 17:36:23",
"2019-07-15 17:37:23",
"2019-07-15 17:38:23",
"2019-07-15 17:39:25",
"2019-07-15 17:40:25"]
}

结果应该是这样的:-

updatetime           ch1[d1]  ch2[d1]  ch1[d2]  ch1[d3]
2019-07-15 17:20:16   21        55       nan        nan
2019-07-15 17:20:23   22        55       nan        nan
2019-07-15 17:21:23   31        56       nan        nan
2019-07-15 17:22:23   31        57       nan        nan
2019-07-15 17:23:25   22        88       nan        nan
2019-07-15 17:24:25   11        90       nan        nan
2019-07-15 17:25:00   nan       nan      43.21      nan
2019-07-15 17:26:23   nan       nan      55.12      nan
2019-07-15 17:27:23   nan       nan      44.21      nan
2019-07-15 17:28:23   nan       nan      66.21      nan
2019-07-15 17:29:25   nan       nan      11.21      nan
2019-07-15 17:30:25   nan       nan      22.1       nan
2019-07-15 17:35:16   nan       nan      nan        21
2019-07-15 17:36:23   nan       nan      nan        22
2019-07-15 17:37:23   nan       nan      nan        31
2019-07-15 17:38:23   nan       nan      nan        31
2019-07-15 17:39:25   nan       nan      nan        22
2019-07-15 17:40:25   nan       nan      nan        11

我怎样才能达到上述结果，我想我应该使用聚合查询，但我没有任何经验。

Answer 1

不完全是，但你总是可以使用 json。它将为您提供满足您需求所需的所有数据。

db.collection.aggregate([
    {
        $project: {
            unit_id: true,
            updatetime: true,
            data: { $objectToArray: "$data" },
        }
    },
    {
        $project: {
            mydata: {
                $map: {
                    input: "$updatetime",
                    as: "input",
                    in: {
                        unit_id: "$unit_id",
                        date: "$$input",
                        data: {
                            $arrayToObject: {
                                $map: {
                                    input: "$data",
                                    as: "newInput",
                                    in: {
                                        k: "$$newInput.k",
                                        v: { $ifNull: [{ $arrayElemAt: ["$$newInput.v", { $indexOfArray: ["$updatetime", "$$input"] }] }, "nan"] }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    },
    {
        $unwind: "$mydata"
    },
    {
        $group: {
            _id: "result",
            data: {
                $push: "$mydata"
            }
        }
    }
])

响应

{
    "_id" : "result",
    "data" : [
        {
            "unit_id" : 38,
            "date" : "2019-07-15 17:20:16",
            "data" : {
                "ch1" : 21,
                "ch2" : 55
            }
        },
        {
            "unit_id" : 38,
            "date" : "2019-07-15 17:20:23",
            "data" : {
                "ch1" : 22,
                "ch2" : 55
            }
        },
        {
            "unit_id" : 38,
            "date" : "2019-07-15 17:21:23",
            "data" : {
                "ch1" : 31,
                "ch2" : 56
            }
        },
        {
            "unit_id" : 38,
            "date" : "2019-07-15 17:22:23",
            "data" : {
                "ch1" : 31,
                "ch2" : 57
            }
        },
        {
            "unit_id" : 38,
            "date" : "2019-07-15 17:23:25",
            "data" : {
                "ch1" : 22,
                "ch2" : 88
            }
        },
        {
            "unit_id" : 38,
            "date" : "2019-07-15 17:24:25",
            "data" : {
                "ch1" : 11,
                "ch2" : 90
            }
        },
        {
            "unit_id" : 39,
            "date" : "2019-07-15 17:25:00",
            "data" : {
                "ch1" : 43.21
            }
        },
        {
            "unit_id" : 39,
            "date" : "2019-07-15 17:26:23",
            "data" : {
                "ch1" : 55.12
            }
        },
        {
            "unit_id" : 39,
            "date" : "2019-07-15 17:27:23",
            "data" : {
                "ch1" : 44.21
            }
        },
        {
            "unit_id" : 39,
            "date" : "2019-07-15 17:28:23",
            "data" : {
                "ch1" : 66.21
            }
        },
        {
            "unit_id" : 39,
            "date" : "2019-07-15 17:29:25",
            "data" : {
                "ch1" : 11.21
            }
        },
        {
            "unit_id" : 39,
            "date" : "2019-07-15 17:30:25",
            "data" : {
                "ch1" : 22.1
            }
        },
        {
            "unit_id" : 40,
            "date" : "2019-07-15 17:35:16",
            "data" : {
                "ch1" : 22
            }
        },
        {
            "unit_id" : 40,
            "date" : "2019-07-15 17:36:23",
            "data" : {
                "ch1" : 31
            }
        },
        {
            "unit_id" : 40,
            "date" : "2019-07-15 17:37:23",
            "data" : {
                "ch1" : 31
            }
        },
        {
            "unit_id" : 40,
            "date" : "2019-07-15 17:38:23",
            "data" : {
                "ch1" : 22
            }
        },
        {
            "unit_id" : 40,
            "date" : "2019-07-15 17:39:25",
            "data" : {
                "ch1" : 11
            }
        },
        {
            "unit_id" : 40,
            "date" : "2019-07-15 17:40:25",
            "data" : {
                "ch1" : "nan"
            }
        }
    ]
}