无法使用node.js在MongoDB中基于特定字符串对值进行分组？

debugcn 发表于 Dev

莎兰·凯南

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

每个模板由devices每个device组成的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
}
}])

阿拉文

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

因此，请参考以下聚合代码。

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。