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?

"bookingdatetime" : "2012-10-11T07:00:00Z" is that a String? — Lalit Agarwal, Dec 08 '14 at 20:30
You need to convert it to an ISODate and then do aggregation on it. Or else, use Map/Reduce — Lalit Agarwal, Dec 08 '14 at 20:42
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") — user1584253, Dec 08 '14 at 20:51
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 } — user1584253, Dec 08 '14 at 21:15

score 30 · Answer 1 · edited Apr 24 '18 at 08:27

30

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

edited Apr 24 '18 at 08:27

A. Morales

123
2
19

answered Dec 08 '14 at 20:39

Will Shaver

12,471
5
49
64

2

It is giving this exception "exception: can't convert from BSON type String to Date" – user1584253 Dec 08 '14 at 20:41
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: http://stackoverflow.com/questions/15473772/how-to-convert-from-string-to-date-data-type – Will Shaver Dec 08 '14 at 20:50
1

@user1584253 probably forgot leading "$" on field (e.g. $bookingdatetime) – Ed Staub Oct 24 '15 at 04:09
13

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" } }` – Ed Staub Oct 24 '15 at 04:12
@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. http://stackoverflow.com/questions/38437797/filter-and-re-arrange-results-using-node-js-and-mongodb-according-to-datemonth/38438154#38438154 – Chanaka De Silva Jul 18 '16 at 17:37
1

The query is wrong and will result in MongoError: FieldPath field names may not start with '$'. See Eb Staubs comment for a correct solution. – Adam Reis Jan 16 '20 at 21:33

score 26 · Accepted Answer · answered Dec 08 '14 at 20:57

26

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
}

answered Dec 08 '14 at 20:57

JohnnyHK

305,182
66
621
471

1

@JohnnyHK , 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. http://stackoverflow.com/questions/38437797/filter-and-re-arrange-results-using-node-js-and-mongodb-according-to-datemonth/38438154#38438154 – Chanaka De Silva Jul 18 '16 at 17:34
Assuming you're only interested in the current year, won't this break down as soon as the year rolls over? – colefner Oct 27 '17 at 18:54
@colefner You could add a `$match` stage at the beginning of the pipeline to filter the docs to the current year, if need be. – JohnnyHK Oct 28 '17 at 16:40

score 3 · Answer 3 · edited Dec 04 '21 at 09:23

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 }

score 2 · Answer 4 · answered Dec 04 '21 at 09:37

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)

Group and count by month

4 Answers4

Linked

Related