Cannot group values based on specific string in MongoDB using node.js?

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.

Following are the sample data of my collections.

Template Data

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

Dynamic Collection Data

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

Group Data

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

So for the above data, I am trying to get the sum of heat and humidity for the groupName specified. So the expected output should be :

Expected Output

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

**Returned Output **

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

I'm not sure how to achieve that, Can any one help me solve the issue

My Query: Here, templates and groups are different collections.

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

as far as I understood, you were expecting the data grouped by group names. But you were getting wrong results.

So for that, please refer this following aggregation code.

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

You will get the answer something like this,

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

You can refer to this link too, https://mongoplayground.net/p/hr95U1eT5bL

db.collection.distinct(), Finds the distinct values for a specified field across a single collection or view and the aggregation pipeline to retrieve distinct values using the $group operator, language-specific rules for string comparison, such as rules for lettercase and For example, you cannot specify different collations per field, or if performing a� The _id field of each output document contains the unique group by value. The output documents can also contain computed fields that hold the values of some accumulator expression . Note

A simple $group stage should do the trick:

db.collection.aggregate([
    {
        $group: {
            _id: "$groupData.groupName",
            heat: {$sum: "$heat"},
            humidity: {$sum: "$humidity"},
            count: {$sum: 1}
        }
    }
])

MongoDB Group by Multiple Fields Using Aggregation Function , Using aggregate operation will clear the concept of Mongodb group by multiple fields. correspond to SUM, but it cannot correspond to a specific column. If a single-node pipeline exceeds the limit, MongoDB generates an error. a specific value for the name field, add that document to the group and� The MongoDB Node.js Driver allows you to easily interact with MongoDB databases from within Node.js applications. You’ll need the driver in order to connect to your database and execute the queries described in this Quick Start series.

You could use $group stage of mongo pipeline framework. Something like this should work

db.collection.aggregate([{
    "$group": {
        "_id": "$groupData.groupName",
        "humidity": {
            "$sum": "$humidity"
        },
        "heat": {
            "$sum": "$heat"
        },
        "count": {
            "$sum": 1
        }
    }
}])

Collection() — MongoDB Node.JS Driver 1.4.9 documentation, readPreference {String}, the prefered read preference (ReadPreference. Inserts a single document or a an array of documents into MongoDB. write waits for fsync before returning, from MongoDB 2.6 on, fsync cannot be The distinct command returns returns a list of distinct values for the given key across a collection. To select data from a collection in MongoDB, we can use the findOne() method. The findOne() method returns the first occurrence in the selection. The first parameter of the findOne() method is a query object. In this example we use an empty query object, which selects all documents in a collection (but returns only the first document).

Node.js v14.7.0 Documentation, Working with JavaScript values and abstract operations process.setgid(id); process.setgroups(groups); process.setuid(id); process. Instead, consider adding a comment next to the specific code path that should not reject and keep error messages as expressive as possible. If specified, error error cannot be a string. Connect Mongoose With the Node.js Application. Now, we have installed mongoose in our application, we just have to connect it with the application backend API. Before connect the mongoose with the node.js first, make sure you have installed node.js and cre ated a server using the node.js. We can get all the feature of mongoose by adding below line

Express Tutorial Part 3: Using a Database (with Mongoose), Some ORMs are tied to a specific database, while others provide a Mongoose: Mongoose is a MongoDB object modeling tool designed to work in Sequelize is a promise-based ORM for Node.js and io.js. We have also decided to have a model for the genre so that values can be created dynamically. MongoDB will ignore authSource values if no username is provided, either in the connection string or via the --username parameter. authMechanism¶ Specify the authentication mechanism that MongoDB will use to authenticate the connection. Possible values include: SCRAM-SHA-1; SCRAM-SHA-256 (Added in MongoDB 4.0) MONGODB-X509; MONGODB-AWS (Added

Why Use Node.js? A Comprehensive Tutorial with Examples, Additionally, it's all based on the open web stack (HTML, CSS and JS) running in Express.js. mongodb and mongojs - MongoDB wrappers to provide the API for MongoDB picks up the value from the input field (i.e., the message text), and emits a Believing that one can not use the new magic Node for everything just � The operation replaces the value of: quantity to 500; the details field to a new embedded document, and the tags field to a new array. Set Fields in Embedded Documents ¶ To specify a <field> in an embedded document or in an array, use dot notation .

Comments
  • Cannot group values ...: What is the issue? Here is an example on how to count and sum using $group.
  • "templates and groups are different collections.": Please post the structures of both the collections (and title them).
  • @prasad_ I have updated the question, please check it now.
  • @prasad_ please check this one stackoverflow.com/a/59561287/10465501
  • I have already tried that but it was giving the duplicate values. For ex, if we take heat of Flat group, it's giving the sum of heat of all other groups as well. i.e, sum of heat of all groups is displaying in every group.
  • Thats not possible, i recommend you look at the raw data to verify you need a different result than what your getting.
  • its hard to tell without seeing the data, but with ur current query you risk "looking up" multiple instances of the same document, this might be the reason why your not getting the result you expect.
  • please check this one stackoverflow.com/a/59561287/10465501
  • its working as expected, all the data shares the same template ID meaning they're gonna get duplicated exactly the same. the problem is not the group but how you built your data.