Group and count by month

Question

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?

Bannerol

"bookingdatetime" : "2012-10-11T07:00:00Z" is that a String? – Rau

You need to convert it to an ISODate and then do aggregation on it. Or else, use Map/Reduce – Rau

the string is converted to ISODate using new Date($bookingdate). new Date("2012-10-11T07:00:00Z") this is the output ISODate("2012-10-11T07:00:00Z") – Bannerol

I have us this query: db.booking.aggregate({$project: {month:{$month:new Date("$bookingdatetime")}}}, {$group:{_id:{month:"$month"},numberofbookings:{$sum:1}}} But its giving me wrong output: { "_id" : { "month" : 8 }, "numberofbookings" : 3 } – Bannerol

Answer 1

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
}

Indraft

@Indraft , there's a question same as this. But the different is it need to return 0 if no values in a certain month..... plz check this and give an answer. #38438297 – Telephonist

Assuming you're only interested in the current year, won't this break down as soon as the year rolls over? – Loanloanda

@Loanloanda You could add a $match stage at the beginning of the pipeline to filter the docs to the current year, if need be. – Indraft

Answer 2

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} 
    }}
]);

Reynoso

It is giving this exception "exception: can't convert from BSON type String to Date" – Bannerol

I didn't see that you're storing the dates as a string. That's holding you up, there's no easy way to do this conversion inside an aggregate statement. There are lots of people who've faced this problem on SO already: #15474272 – Reynoso

@Bannerol probably forgot leading "$" on field (e.g. $bookingdatetime) – Rocketeer

I'd guess that the OP doesn't want all Octobers folded together into the same group, so I'd suggest _id: { month: { $month: "$bookingdatetime" }, year: { $year: "$bookingdatetime" } } – Rocketeer

@WillShaver In this solution, we only can get months in the database. what if we want to get 0 to the months not in the result set. ?? Please check this question and give an answer. #38438297 – Telephonist

The query is wrong and will result in MongoError: FieldPath field names may not start with '$'. See Eb Staubs comment for a correct solution. – Bilharziasis

Answer 3

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 }

Nance

Answer 4

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)

Eskil