MongoDB Group by Date

Introduction

Grouping by date is super common when retrieving data from your database. The date functions are immensely used in modern web development. Dates are valid data types in MongoDB. We can store dates in MongoDB using date or ISODate function. Storing data along with dates is useful in various ways. We can sort the data in any way. We can group-by the data according to dates. We can also point-out data using dates. This all can be done using the mongo shell. In this article, we will learn about how to MongoDB group by date. We will do this by using the mongo shell only.

Group-by date using Mongo shell

First, we need a collection in which we have data that can be grouped according to dates.

> db.sales.find().pretty()
{
        "_id" : ObjectId("5d4c767240ed38840a77bc61"),
        "item" : "Mobile Phones",
        "price" : 20000,
        "quantity" : 2,
        "date" : ISODate("2019-08-05T08:00:00Z")
}
{
        "_id" : ObjectId("5d4c76a340ed38840a77bc62"),
        "item" : "TV set",
        "price" : 15000,
        "quantity" : 3,
        "date" : ISODate("2019-08-05T08:00:00Z")
}
{
        "_id" : ObjectId("5d4c76bc40ed38840a77bc63"),
        "item" : "Laptop",
        "price" : 50000,
        "quantity" : 5,
        "date" : ISODate("2019-06-01T08:00:00Z")
}
{
        "_id" : ObjectId("5d4c76fe40ed38840a77bc64"),
        "item" : "Furniture",
        "price" : 20000,
        "quantity" : 1,
        "date" : ISODate("2019-06-01T08:00:00Z")
}
{
        "_id" : ObjectId("5d4c772640ed38840a77bc65"),
        "item" : "Sofa set",
        "price" : 10000,
        "quantity" : 2,
        "date" : ISODate("2019-02-09T08:00:00Z")
}
>

In the sales collection, we have five documents containing data of five different items. We can see in each document, there is a field named as date, that contains an ISODate function. A string containing date and time (UTC) is passed in each ISODate function. We will learn how we can group data by dates in MongoDB using mongo shell.

We can group-by the data according to the dates by using the aggregate function. It is a bit complex command.

db.sales.aggregate([
  {
    $group: {
      _id: {
        month: { $month: "$date" },
        day: { $dayOfMonth: "$date" },
        year: { $year: "$date" }
      },
      totalPrice: { $sum: { $multiply: ["$price", "$quantity"] } },
      count: { $sum: 1 }
    }
  }
]);

Observe the above command. We used an aggregate function with the sales collection. In the aggregate function, we used the group operator with a dollar ($) sign. The group operator will stage the documents according to the instruction we specify with it. We used the _id to specify the expression. It is mandatory to have _id but we can also leave it as null. But while grouping by date, we have to use _id to specify the date format. Have a look at the following the format we used.

_id : { month: { $month: "$date" }, day: { $dayOfMonth: "$date" }, year: { $year: "$date" } }

We have used month, dayOfMonth and year operators to fetch the specific data. The month, day and year will be displayed in the output because of these operators. Now we have the data with the same dates but how the data should be grouped? We can group the items with the same date by adding their total price. By total price, I mean price multiplied by quantity and this is what we exactly did with the group operator.

totalPrice: {
  $sum: {
    $multiply: ["$price", "$quantity"];
  }
}

We created a totalPrice field that will appear in the output. We multiply the price and quantity of each item on the same day and then add them using the sum operator. This is how the data will be grouped in the output. Apart from these, we also have a count field that counts, how many items are there in one group.

count: {
  $sum: 1;
}

We initialized the sum operator as 1 because there will always be at least a single item on each date. Let’s see this command in action.

> db.sales.aggregate(
    [{
        $group : {
            _id : { month: { $month: "$date" }, day: { $dayOfMonth: "$date" }, year: { $year: "$date" } },
            totalPrice: { $sum: { $multiply: [ "$price", "$quantity" ] } },
            count: { $sum: 1 }
                }
            }
    ] )
{ "_id" : { "month" : 2, "day" : 9, "year" : 2019 }, "totalPrice" : 20000, "count" : 1 }
{ "_id" : { "month" : 6, "day" : 1, "year" : 2019 }, "totalPrice" : 270000, "count" : 2 }
{ "_id" : { "month" : 8, "day" : 5, "year" : 2019 }, "totalPrice" : 85000, "count" : 2 }
>

Yes, all the items are group-by according to the dates. We can also see the total price of all the items having the same date and the count of items on each date.

Conclusion

The dates play an important part in databases on the modern web. They are very useful while sorting and arranging. MongoDB provides the aggregate operation and the group operator to group by data according to the dates. We hope you can apply what you’ve learned here to your specific application.

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.