How to use mongoose to group by date
Introduction
Dates are an important part of database work. They are often used to keep the track of records. There are a lot of ways to work with dates at the backend. MongoDB also supports date. One of the uses of dates is to group records. In mongoose, we can use the $group stage of aggregation. In this article, we will discuss how to use mongoose to group by date.
We will use the sales collection for the demonstration.
1 2 3 4 5 6 7 | { "_id" : ObjectId("5e3282d442b34ae602c1bb32"), "name" : "Iphone 11", "unitsSold" : 3, "date" : ISODate("2014-04-03T08:00:00Z") } { "_id" : ObjectId("5e3282e942b34ae602c1bb33"), "name" : "Iphone 11 pro", "unitsSold" : 7, "date" : ISODate("2014-07-19T08:00:00Z") } { "_id" : ObjectId("5e3282f842b34ae602c1bb34"), "name" : "Samsung S10", "unitsSold" : 3, "date" : ISODate("2014-04-03T08:00:00Z") } { "_id" : ObjectId("5e32832e42b34ae602c1bb35"), "name" : "Samsung S10", "unitsSold" : 2, "date" : ISODate("2014-09-09T08:00:00Z") } { "_id" : ObjectId("5e32833c42b34ae602c1bb36"), "name" : "Iphone 11 pro max", "unitsSold" : 1, "date" : ISODate("2014-07-19T08:00:00Z") } { "_id" : ObjectId("5e32834b42b34ae602c1bb37"), "name" : "Iphone 11", "unitsSold" : 5, "date" : ISODate("2014-09-09T08:00:00Z") } { "_id" : ObjectId("5e32835a42b34ae602c1bb38"), "name" : "Iphone 11 pro", "unitsSold" : 7, "date" : ISODate("2014-07-19T08:00:00Z") } |
There are three fields in each document – name, unitsSold, and date. Each document states how many units of smartphones were sold on a specific date. The date is in the ISODate format. We will group this data, displaying the total units sold on a particular date.
For performing HTTP endpoint testing, we will use the postman tool. You can download the postman tool from www.getpostman.com.
$group
As mentioned earlier, we will use the $group stage of aggregation. So let’s start by creating a route handler.
1 | router.route("/find").get(function(req, res) {}); |
The above route handler will be invoked when the route ‘/find’ will be executed. Let’s create aggregation on the sales collection.
1 2 3 4 5 6 7 8 9 10 11 12 13 | sales.aggregate( ,function(err, result){ if(err){ res.send(err) } else{ res.json(result) } }) |
We have to pass the $group stage as the first argument. It should be an array of objects. Let’s do it.
1 2 3 4 5 6 7 | [ { $group: { _id: { $dateToString: { format: "%Y-%m-%d", date: "$date" } } } } ]; |
The data will be grouped according to the value specified inside the “_id” field. Observe it carefully.
1 | _id : { $dateToString: { format: "%Y-%m-%d", date: "$date" } } |
We used the dateToString to convert the ISODate format into a specified format. Let’s add this to the route handler.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | router.route("/find").get(function(req, res) { sales.aggregate( [ { $group: { _id: { $dateToString: { format: "%Y-%m-%d", date: "$date" } } } } ], function(err, result) { if (err) { res.send(err); } else { res.json(result); } } ); }); |
This should provide the groups we want. Let’s execute this route using the postman tool and see the results.
Yes! We have three dates. The next step is to calculate the total units sold on each date. We can do this with the help of the $sum operator.
1 2 3 | "totalUnitsSold" : { $sum : "$unitsSold" } |
The total number of units sold on a particular day will be stored in a new field we just created – “totalUnitsSold”. The value of the $sum operator will be the “unitsSold” field beginning with a dollar($) sign. Now we can complete the route handler by adding this to it.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | router.route("/find").get(function(req, res) { sales.aggregate( [ { $group: { _id: { $dateToString: { format: "%Y-%m-%d", date: "$date" } }, totalUnitsSold: { $sum: "$unitsSold" } } } ], function(err, result) { if (err) { res.send(err); } else { res.json(result); } } ); }); |
It’s complete now. Let’s execute it using the postman tool.
Yes! It works perfectly. The result is grouped by date properly.
Conclusion
The dates are an important part of development. The concept of the group by is frequently used with dates. So this article describes how to use dates and group by together along with the $sum operator in mongoose.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started