How to get the aggregation values grouped by group names in mongodb using node.js?

Question

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.

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.

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.

Template Data

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

Dynamic Collection Data

[
  {
    "_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
   }]

Group Data

[{
   "_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}
          }
      }]));

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
  }]

Expected Output

[
  {
    "_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?

Answer 1

Update:

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.

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

The query would be,

[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
  }]

Answer 2

You can add $group as a next aggregation stage and use $sum both for counting and adding up:

{
    $group: {
        _id: "$group.groupName",
        "count": { $sum: 1 },
        "heat": { $sum: "$heat" },
        "humidity": { $sum: "$humidity" }
    }
}