MongoDB Aggregation: Group by Date and Count Documents

To create a chart showing the number of orders placed each day, you can use the MongoDB aggregation framework. The $group stage can be used to group documents by date, but as you've noticed, it groups by the exact date, including the time.

Problem with Exact Date Grouping

When you group by the exact date, including the time, you will get a separate group for each unique date and time combination. This is because MongoDB stores dates as a BSON Date type, which includes both date and time information.

Solution: Use $dateToString or $dateTrunc

To group documents by date only, you can use the $dateToString or $dateTrunc operators. These operators allow you to extract the date part from a date field and group by that.

Using $dateToString

You can use $dateToString to convert the date field to a string in the format YYYY-MM-DD. Here is an example:

1db.orders.aggregate([
2  {
3    $group: {
4      _id: { $dateToString: { format: "%Y-%m-%d", date: "$orderDate" } },
5      count: { $sum: 1 }
6    }
7  }
8])

1db.orders.aggregate([
2  {
3    $group: {
4      _id: { $dateToString: { format: "%Y-%m-%d", date: "$orderDate" } },
5      count: { $sum: 1 }
6    }
7  }
8])

This will group the documents by the date part of the orderDate field and count the number of documents in each group.

Using $dateTrunc

Alternatively, you can use $dateTrunc to truncate the date field to the day level. Here is an example:

1db.orders.aggregate([
2  {
3    $group: {
4      _id: { $dateTrunc: { date: "$orderDate", unit: "day" } },
5      count: { $sum: 1 }
6    }
7  }
8])

1db.orders.aggregate([
2  {
3    $group: {
4      _id: { $dateTrunc: { date: "$orderDate", unit: "day" } },
5      count: { $sum: 1 }
6    }
7  }
8])

This will also group the documents by the date part of the orderDate field and count the number of documents in each group.

Example Output

Both of these examples will produce a similar output, which will be a list of documents with the date and the count of orders for that date. For example:

1[
2  {
3    "_id": "2022-01-01",
4    "count": 10
5  },
6  {
7    "_id": "2022-01-02",
8    "count": 15
9  },
10  {
11    "_id": "2022-01-03",
12    "count": 20
13  }
14]

1[
2  {
3    "_id": "2022-01-01",
4    "count": 10
5  },
6  {
7    "_id": "2022-01-02",
8    "count": 15
9  },
10  {
11    "_id": "2022-01-03",
12    "count": 20
13  }
14]

This output can be used to create a chart showing the number of orders placed each day.