mongoose Aggregate Sum
Introduction
Aggregation in MongoDB provides a vast range of very useful operators. One such operator is $sum. As the name suggests, the $sum operator is used to get the sum of numerical values. It is commonly used with $match and $group. Other available stages are $addFields (Available starting in MongoDB 3.4), $set (MongoDB 4.2), $replaceRoot (Available starting in MongoDB 3.4), $replaceWith (Available starting in MongoDB 4.2), and $match stage that includes an $expr expression.
In this article, we will discuss how to use mongoose to get a aggregate sum using $sum with $group. We will use the store collection for the demonstration.
1 2 3 4 5 6 7 | { "_id" : ObjectId("5e2eaa45ee1f22706dac169f"), "name" : "Iphone 11", "company" : "Apple", "quantity" : 2 } { "_id" : ObjectId("5e2eaa53ee1f22706dac16a0"), "name" : "Iphone 11 pro max", "company" : "Apple", "quantity" : 3 } { "_id" : ObjectId("5e2eaa77ee1f22706dac16a1"), "name" : "S10", "company" : "Samsung", "quantity" : 1 } { "_id" : ObjectId("5e2eab35ee1f22706dac16a2"), "name" : "P30 pro", "company" : "Huawei", "quantity" : 4 } { "_id" : ObjectId("5e2eab44ee1f22706dac16a3"), "name" : "S10 plus", "company" : "Samsung", "quantity" : 2 } { "_id" : ObjectId("5e2eab62ee1f22706dac16a4"), "name" : "Iphone 11 pro", "company" : "Apple", "quantity" : 2 } { "_id" : ObjectId("5e2eab84ee1f22706dac16a5"), "name" : "P30", "company" : "Huawei", "quantity" : 3 } |
Each document in the store collection has three fields – name, company, and quantity. First, we will group these documents by the company field. Then, we will add the quantity of each company in the collection.
For performing HTTP endpoint testing, we will use the postman tool. You can download the postman tool from www.getpostman.com.
$group
The first thing is to group by all the documents. Let’s create a route handler first.
1 | router.route("/sum").get(function(req, res) {}); |
The above route handler will be invoked when the route ‘/sum’ is executed. We will use the aggregate here.
1 2 3 4 5 6 7 | store.aggregate([], function(err, result) { if (err) { res.send(err); } else { res.json(result); } }); |
As of now, there is nothing used in the aggregation. Let’s add $group inside it.
1 2 3 4 5 6 7 | [ { $group: { _id: "$company" } } ]; |
Pay attention here. The _id field of the $group specifies how to group by the collection. In our case, it is the “company” field of each document. Another point to note here is that we cannot simply give “company” as the value of the _id field. We have to write the $ sign followed by the “company”. Let’s add this to the aggregate.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | router.route("/sum").get(function(req, res) { store.aggregate( [ { $group: { _id: "$company" } } ], function(err, result) { if (err) { res.send(err); } else { res.json(result); } } ); }); |
Let’s execute this route using the postman tool and see if it works or not.
It works perfectly. There are three companies – Apple, Samsung, and Huawei. The next step is to add the quantity of each smartphone of each company.
$sum
Let’s add a $sum in it.
1 2 3 | "total" : { $sum : "$quantity" } |
We have to give a name to the field that will contain the total quantity. In our case, I name it “total”. Similar to the $group, we will again use the $sign followed by “quantity”. This part will be placed with $group.
1 2 3 4 5 6 | $group : { "_id": "$company", "total" : { $sum : "$quantity" } } |
This is perfect. Let’s add it to the route handler.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | router.route("/sum").get(function(req, res) { store.aggregate( [ { $group: { _id: "$company", total: { $sum: "$quantity" } } } ], function(err, result) { if (err) { res.send(err); } else { res.json(result); } } ); }); |
Let’s execute this route using the postman tool.
It works perfectly!
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started