javascript - 无法使用 node.js 根据 MongoDB 中的特定字符串对值进行分组？

Question

我有一个用例，其中有 3 个集合。即模板、组和每个模板的 1 个动态集合，这些集合将在创建模板后立即创建。

每个模板由设备组成，每个设备由Groups集合的groupId组成。动态集合存储模板设备推送的数据。

以下是我收集的样本数据。

模板数据

{
  "_id": "5e0ae38729218b0a3861118b",
  "templateId": "13435158964",
  "devices": [
    {
      "deviceId": "a-1",
      "_id": "5e0ae49629218b0a3861118f",
      "group": "5e0ae41d29218b0a3861118d",
    },{
       "deviceId": "sb-0001",
       "_id": "5e0af166981f39410cd89b72",
       "group": "5e0af11d981f39410cd89b70"
    }]
}

动态采集数据

[
  {
      "_id": "5e0ae793b1384737a4f855cf",
      "template": "13435158964",
      "deviceId": "a-1",
      "heat": 30,
      "humidity": 40
    },
    {
      "_id": "5e0ae7a2b1384737a4f855d0",
      "template": "13435158964",
      "deviceId": "sb-0001",
      "heat": 40,
      "humidity": 20
    },
    {
      "_id": "5e0ae890b1384737a4f855d3",
      "template": "13435158964",
      "deviceId": "a-1",
      "heat": 10,
      "humidity": 20
    },
    {
      "_id": "5e0af188981f39410cd89b73",
      "template": "13435158964",
      "deviceId": "a-1",
      "heat": 60,
      "humidity": 50
    },
    {
      "_id": "5e0af196981f39410cd89b74",
      "template": "13435158964",
      "deviceId": "sb-0001",
      "heat": 15,
      "humidity": 25
    }]

群组数据

[{
   "_id": "5e0af11d981f39410cd89b70",
   "template": "5e0ae38729218b0a3861118b",
   "groupName": "Flats"
 },{
    "_id": "5e0ae41d29218b0a3861118d",
    "template": "5e0ae38729218b0a3861118b",
    "groupName": "Swimming Pool"
 }]

因此，对于上述数据，我试图获取指定的 groupName 的热量和湿度之和。所以预期的输出应该是:

预期输出

[
  {
    "_id": "Swimming Pool",
    "heat": 110,
    "humidity": 165,
    "count":2
  },{
    "_id": "Flat",
    "heat": 70,
    "humidity": 80,
    "count":1
  }]

**返回的输出**

[
  {
    "_id": "Swimming Pool",
    "heat": 180,
    "humidity": 245,
    "count":2
  },{
    "_id": "Flat",
    "heat": 180,
    "humidity": 245,
    "count":1
  }]

我不知道如何实现这一点，谁能帮我解决这个问题

我的查询:在这里，模板和组是不同的集合。

db.getCollection('13435158964').aggregate([
{ "$match": {"entryDayTime":{
                    $lt: new Date("2019-11-29T18:30:00.000Z"),
                    $gte: new Date("2019-10-31T18:30:00.000Z")
                }
}
},{
    $lookup:{
        from:"templates",
        localField:"template",
        foreignField:"templateId",
        as:"templateData"
    }
},{
    $unwind:"$templateData"
},{
    $unwind:"$templateData.devices"
},{
    $lookup:{
       from:"groups",
       localField:"templateData.devices.group",
       foreignField:"_id",
       as:"groupData"
     }
},{
    $unwind:"$groupData"
},{
    $group:{
       _id: "$groupData.groupName",
       heat:{$sum:"$heat"},
       humidity:{$sum:"$humidity"},
       count:{$sum:1}
    }
},{
    $project:{
       "count":1,"heat":1,"humidity":1,"templateData.devices.group":1,"templateData.devices.deviceId":1,"groupData.groupName":1
}
}])

Answer 1

据我了解，您期​​望的是按组名称分组的数据。但你得到了错误的结果。

因此，请引用以下聚合代码。

db.getCollection('13435158964').aggregate([
  {
    $lookup: {
      from: "templates",
      localField: "template",
      foreignField: "templateId",
      as: "templateData"
    }
  },
  {
    $unwind: "$templateData"
  },
  {
    $unwind: "$templateData.devices"
  },
  {
    $match: {
      $expr: {
        $eq: [
          "$deviceId",
          "$templateData.devices.deviceId"
        ]
      }
    }
  },
  {
    $lookup: {
      from: "groups",
      localField: "templateData.devices.group",
      foreignField: "_id",
      as: "groupData"
    }
  },
  {
    $unwind: "$groupData"
  },
  {
    $group: {
      _id: "$groupData.groupName",
      heat: {
        "$sum": "$heat"
      },
      humidity: {
        "$sum": "$humidity"
      },
      count: {
        "$sum": 1
      }
    }
  }
])

你会得到类似这样的答案，

[
  {
    "_id": "Swimming Pool",
    "count": 3,
    "heat": 100,
    "humidity": 110
  },
  {
    "_id": "Flats",
    "count": 2,
    "heat": 55,
    "humidity": 45
  }
]

您也可以引用这个链接，https://mongoplayground.net/p/hr95U1eT5bL