MongoDB Aggregate sum of values in a list of dictionaries for all documents?

MongoDBDatabaseBig Data Analytics

To aggregate sum of values in a list of dictionaries, use $sum along with aggregate(). Let us create a collection with documents −

> db.demo117.insertOne(
...    {
...       ID:101,
...       Details:[
...          {
...             Name:'Chris',
...             Value1:20,
...             Value2:30,
...             Value3:10
...          },
...          {
...             Name:'David',
...             Value1:10,
...             Value2:50,
...             Value3:30
...          }
...       ]
...    }
... );
{
   "acknowledged" : true,
   "insertedId" : ObjectId("5e2f02ced8f64a552dae6361")
}
> db.demo117.insertOne(
...    {
...       ID:102,
...       Details:[
...          {
...             Name:'Chris',
...             Value1:20,
...             Value2:10,
...             Value3:20
...          },
...          {
...             Name:'David',
...             Value1:30,
...             Value2:40,
...             Value3:10
...          }
...       ]
...    }
... );
{
   "acknowledged" : true,
   "insertedId" : ObjectId("5e2f02ced8f64a552dae6362")
}

Display all documents from a collection with the help of find() method −

> db.demo117.find();

This will produce the following output −

{
   "_id" : ObjectId("5e2f02ced8f64a552dae6361"), "ID" : 101, "Details" : [
      { "Name" : "Chris", "Value1" : 20, "Value2" : 30, "Value3" : 10 },
      { "Name" : "David", "Value1" : 10, "Value2" : 50, "Value3" : 30 }
   ]
}
{
   "_id" : ObjectId("5e2f02ced8f64a552dae6362"), "ID" : 102, "Details" : [
      { "Name" : "Chris", "Value1" : 20, "Value2" : 10, "Value3" : 20 },
      { "Name" : "David", "Value1" : 30, "Value2" : 40, "Value3" : 10 }
   ] 
}

Following is the query to aggregate sum of values in a list of dictionaries for all documents −

> db.demo117.aggregate([{
...    $unwind: "$Details"
... }, {
...    $group: {
...       _id: "$Details.Name",
...       "Value1": { $sum: "$Details.Value1" },
...       "Value2": { $sum: "$Details.Value2" },
...       "Value3": { $sum: "$Details.Value3" }
...    }
... }])

This will produce the following output −

{ "_id" : "David", "Value1" : 40, "Value2" : 90, "Value3" : 40 }
{ "_id" : "Chris", "Value1" : 40, "Value2" : 40, "Value3" : 30 }
raja
Published on 30-Mar-2020 12:13:47
Advertisements