CODEWITHSUNDEEP
mongodbdategrouping
user1584253
user1584253

Reputation: 1005

Group and count by month

I have a booking table and I want to get number of bookings in a month i.e. group by month.

And I am confused that how to get month from a date.

Here is my schema:

{
    "_id" : ObjectId("5485dd6af4708669af35ffe6"),
    "bookingid" : 1,
    "operatorid" : 1,
    ...,
    "bookingdatetime" : "2012-10-11T07:00:00Z"
}
{
    "_id" : ObjectId("5485dd6af4708669af35ffe7"),
    "bookingid" : 2,
    "operatorid" : 1,
    ...,
    "bookingdatetime" : "2014-07-26T05:00:00Z"
}
{
    "_id" : ObjectId("5485dd6af4708669af35ffe8"),
    "bookingid" : 3,
    "operatorid" : 2,
    ...,
    "bookingdatetime" : "2014-03-17T11:00:00Z"
}

And this is I have tried:

db.booking.aggregate([
  { $group: {
    _id: new Date("$bookingdatetime").getMonth(),
    numberofbookings: { $sum: 1 }
  }}
])

but it returns:

{ "_id" : NaN, "numberofbookings" : 3 }

Where am I going wrong?

Upvotes: 15

Views: 44836

Answers (4)

Xavier Guihot
Xavier Guihot

Reputation: 61666

If you intend to get groups by months even if your data spreads over multiple years, you can use a combination of $dateFromString and $dateToString (in order to format dates as "%Y-%m" (e.g. 2012-10)):

// { date: "2012-10-11T07:00:00Z" }
// { date: "2012-10-23T18:30:00Z" }
// { date: "2012-11-02T21:30:00Z" }
// { date: "2013-01-11T18:30:00Z" }
// { date: "2013-10-07T14:15:00Z" }
db.bookings.aggregate([
  { $group: {
    _id: {
      $dateToString: {
        date: { $dateFromString: { dateString: "$date" } },
        format: "%Y-%m"
      }
    },
    bookings: { $count: {} } // or { $sum: 1 } prior to Mongo 5
  }}
])
// { _id: "2012-10", bookings: 2 }
// { _id: "2012-11", bookings: 1 }
// { _id: "2013-01", bookings: 1 }
// { _id: "2013-10", bookings: 1 }

This:

  • first transforms the string date into a string: $dateFromString: { dateString: "$date" }
  • in order to format the date as %Y-%m: $dateToString: { date: { }, format: "%Y-%m" }
  • the combination of which ($dateFromString/$dateToString) is used as our group key
  • and finally we count our grouped bookings with $count (or { $sum: 1 } prior to Mongo 5)

Upvotes: 3

Edward Ameir
Edward Ameir

Reputation: 39

Starting in Mongo 4, you can use the $toDate operator to convert your string to date (building on the answer given by Will Shaver):

// { date: "2012-10-11T07:00:00Z" }
// { date: "2012-10-23T18:30:00Z" }
// { date: "2012-11-02T21:30:00Z" }
db.bookings.aggregate([
  { $group: {
    _id: { month: { $month: { $toDate: "$date" } } },
    bookings: { $sum: 1 }
  }}
])
// { "_id" : { "month" : 10 }, "bookings" : 2 }
// { "_id" : { "month" : 11 }, "bookings" : 1 }

Upvotes: 3

Will Shaver
Will Shaver

Reputation: 13081

You need to use the $month keyword in your group. Your new Date().getMonth() call will only happen once, and will try and create a month out of the string "$bookingdatetime".

db.booking.aggregate([
    {$group: {
        _id: {$month: "$bookingdatetime"}, 
        numberofbookings: {$sum: 1} 
    }}
]);

Upvotes: 31

JohnnyHK
JohnnyHK

Reputation: 311865

You can't include arbitrary JavaScript in your aggregation pipeline, so because you're storing bookingdatetime as a string instead of a Date you can't use the $month operator.

However, because your date strings follow a strict format, you can use the $substr operator to extract the month value from the string:

db.test.aggregate([
    {$group: {
        _id: {$substr: ['$bookingdatetime', 5, 2]}, 
        numberofbookings: {$sum: 1}
    }}
])

Outputs:

{
    "result" : [ 
        {
            "_id" : "03",
            "numberofbookings" : 1
        }, 
        {
            "_id" : "07",
            "numberofbookings" : 1
        }, 
        {
            "_id" : "10",
            "numberofbookings" : 1
        }
    ],
    "ok" : 1
}

Upvotes: 26

Related Questions