You can use the following syntax to group documents by date in MongoDB:
db.collection.aggregate([
{$group:{ _id:{$dateToString:{format: "%Y-%m-%d", date: "$day"}}, count:{ $sum: 1}}}
])
Note that day is the name of the date field that we’d like to group by in this example.
The following examples show how to use this syntax with a collection sales with the following documents:
db.sales.insertOne({day: new Date("2020-01-20"), amount: 40})
db.sales.insertOne({day: new Date("2020-01-21"), amount: 32})
db.sales.insertOne({day: new Date("2020-01-22"), amount: 19})
db.sales.insertOne({day: new Date("2020-01-23"), amount: 29})
db.sales.insertOne({day: new Date("2020-01-24"), amount: 35})
Example 1: Group by Date & Count
We can use the following code to count the number of documents, grouped by date:
db.sales.aggregate([
{$group:{ _id:{$dateToString:{format: "%Y-%m-%d", date: "$day"}}, count:{ $sum: 1}}}
])
This query returns the following results:
{ _id: '2020-01-20', count: 2 }
{ _id: '2020-01-22', count: 1 }
{ _id: '2020-01-21', count: 2 }
From the results we can see:
- The date 2020-01-20 occurs 2 times.
- The date 2020-01-22 occurs 1 time.
- The date 2020-01-21 occurs 2 times.
Example 2: Group by Date & Count (Then Sort)
We can use the following code to count the number of documents, grouped by date, and sort the results based on count ascending:
db.sales.aggregate([
{$group:{_id:{$dateToString:{format: "%Y-%m-%d", date: "$day"}}, count:{ $sum: 1}}},
{$sort: {count:1}}
])
This query returns the following results:
{ _id: '2020-01-22', count: 1 }
{ _id: '2020-01-20', count: 2 }
{ _id: '2020-01-21', count: 2 }
To instead sort by count descending, we can use -1 in the sort statement:
db.sales.aggregate([
{$group:{_id:{$dateToString:{format: "%Y-%m-%d", date: "$day"}}, count:{ $sum: 1}}},
{$sort: {count:-1}}
])
This query returns the following results:
{ _id: '2020-01-20', count: 2 }
{ _id: '2020-01-21', count: 2 }
{ _id: '2020-01-22', count: 1 }
Note: You can find the complete documentation for the sort function here.
Additional Resources
The following tutorials explain how to perform other common operations in MongoDB:
MongoDB: How to Add a New Field
MongoDB: How to Remove a Field
MongoDB: How to Group By and Count
MongoDB: How to Group By Multiple Fields