MongoDB: How to Group by Date


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

Leave a Reply

Your email address will not be published.