如何使用node.js在mongodb中获取按组名分组的聚合值?

问题描述

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

I've a usecase where I'm having 3 collections. i.e, Templates,Groups and 1-dynamic collection of each template, which will be created right after the creation of a template.

每个template由devices组成，每个device由Groups集合的groupId组成.动态收集存储模板设备推送的数据.

Each template consists of devices and each device consists of a groupId of Groups collection. Dynamic collection stores the data pushed by the template's devices.

现在，我需要找出Groups集合中设备grouped by groupName的合计(求和)值.

Now I need to find out the aggregation(sum) values of devices grouped by groupName of Groups collection.

以下是我的收藏的样本数据.

Following are the sample data of my collections.

模板数据

{
  "_id": "5e0ae38729218b0a3861118b",
  "templateId": "27127206822",
  "devices": [
    {
      "deviceId": "waterTest",
      "_id": "5e0ae49629218b0a3861118f",
      "group": "5e0ae41d29218b0a3861118d",
    },{
       "deviceId": "Test",
       "_id": "5e0af166981f39410cd89b72",
       "group": "5e0af11d981f39410cd89b70"
    }]
}

动态收集数据

[
  {
    "_id": "5e0ae793b1384737a4f855cf",
    "template": "27127206822",
    "deviceId": "waterTest",
    "heat" : 20,
    "humidity" : 10
   },{
      "_id": "5e0ae7a2b1384737a4f855d0",
      "template": "27127206822",
      "deviceId": "waterTest",
      "heat" : 40,
      "humidity" : 20
   },{
      "_id": "5e0ae890b1384737a4f855d3",
      "template": "27127206822",
      "deviceId": "waterTest",
      "heat" : 60,
      "humidity" : 50
    },{
       "_id": "5e0af188981f39410cd89b73",
       "template": "27127206822",
       "deviceId": "Test",
       "heat": 60,
       "humidity": 50
   },{
      "_id": "5e0af196981f39410cd89b74",
      "template": "27127206822",
      "deviceId": "Test",
      "heat": 10,
      "humidity": 20
   }]

组数据

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

现在看看我到目前为止编写的聚合查询.

Now look at the aggregate query I've written so far.

let templateId = "27127206822"; // Dynamic Collection

[err, templateData] = await to(mongoose.connection.db.collection(templateId)
  .aggregate([
     {
          $lookup:{
             from:"templates",
             localField:"template",
             foreignField:"templateId",
             as:"template"
          }
      },{ 
          $unwind:"$template"
      },{ 
          $unwind:"$template.devices"
      },{
          $lookup:{
             from:"groups",
             localField:"template.devices.group",
             foreignField:"_id",
             as:"group"
          }
      },{
          $unwind:"$group"
      },{
          $group:{
             _id: "$groupData.groupName",
             heat:{$sum:"$heat"},
             humidity:{$sum:"$humidity"},
             count:{$sum:1}
          }
      }]));

现在，我需要获取按Group集合的groupName分组的每个device的heat & humidity之和.但是我得到了错误的数据.即，一个组的总和也将添加到所有其他组中.

Now, I need to get the sum of heat & humidity of every device grouped by groupName of Group collection. But I'm getting the wrong data. i.e, one group's sum is getting added to all other groups as well.

**返回的输出**

**Returned Output **

//Am getting this Data
[
  {
    "_id": "Flats",
    "heat": 190,  // 2 group's data getting added in both the groups
    "humidity": 150,
    "count":5
  },{
    "_id": "SPool",
    "heat": 190,
    "humidity": 150,
    "count":5
  }]

预期产量

[
  {
    "_id": "Flats",
    "heat": 70,
    "humidity": 70,
    "count":2
  },{
    "_id": "SPool",
    "heat": 120,
    "humidity": 80,
    "count":3
  }]

要获得所需的结果，我有什么想念的吗?

Is there anything I've missed to get the required result?

推荐答案

更新 :

Update:

我解决了这个问题.一旦完成$unwind:"$template.devices"，每个文档都将获取$template.devices阵列的每个设备，并且每个文档都将被复制.

I resolved this issue. Once the $unwind:"$template.devices" is done, every document gets each and every device of $template.devices array and with that documents gets duplicated.

因此，为了获得正确的答案，我们需要将deviceId与每个文档的deviceId of template.devices对象进行匹配.

So in order to get the correct answers, we need to match deviceId with the deviceId of template.devices object of every document.

查询应为

[err, templateData] = await to(mongoose.connection.db.collection(templateId)
  .aggregate([
     {
          $lookup:{
             from:"templates",
             localField:"template",
             foreignField:"templateId",
             as:"template"
          }
      },{ 
          $unwind:"$template"
      },{ 
          $unwind:"$template.devices"
      },{
          $match:{
            $expr:{
              $eq: [ "$deviceId","$template.devices.deviceId" ]
            }
          }
      },{
          $lookup:{
             from:"groups",
             localField:"template.devices.group",
             foreignField:"_id",
             as:"group"
          }
      },{
          $unwind:"$group"
      },{
          $group:{
             _id: "$groupData.groupName",
             heat:{$sum:"$heat"},
             humidity:{$sum:"$humidity"},
             count:{$sum:1}
          }
      }]));

使用此方法，我们将获得预期的输出.

By using this, we'll get the expected output. Which is,

[
  {
    "_id": "Flats",
    "heat": 70,
    "humidity": 70,
    "count":2
  },{
    "_id": "SPool",
    "heat": 120,
    "humidity": 80,
    "count":3
  }]

这篇关于如何使用node.js在mongodb中获取按组名分组的聚合值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！