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?
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}
}}
]);
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
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With