MongoDB - 按月分组 [英] MongoDB - group by month
问题描述
这是我的架构:
{
_id:ObjectId(5485dd6af4708669af35ffe6),
bookingid:1,
operatorid:1,
clientid null,
callname:Sayem Hussain,
contactno:0205661862,
regular:0,
bookingdetail:[
{
driverid:1,
pickupdatetime:2011-04-14 11:00:00,
from:white chapel,
到:海滩,
费用:500,
旅途时间:60
},
{
driverid:2,
pickupdatetime:2012-05-14 12:00:00,
from:walthamstow,
to:mile end,
cost :1000,
旅途时间:50
}
],
bookingdatetime:2012-10-11T07:00:00Z
}
{
_id:ObjectId(5485dd6af4708669af35ffe7),
subscriptionid:2,
operatorid:1,
clientid:1,
callername ,
contactno:0205561281,
regular:1,
bookingdetail:[
{
driverid:3,
pickupdatetime:2012-02-12 09:00:00,
from:grange park,
to:queen mary,
cost :650,
旅程时间:90
},
{
driverid:2,
pickupdatetime:2012-02-13 06: 00:00,
from:drapers bar,
to:naveed restaurant,
cost:1350,
journeytime:120
}
],
bookingdatetime:2014-07-26T05:00:00Z
}
{
_id:ObjectId 5485dd6af4708669af35ffe8),
bookingid:3,
operatorid:2,
clientid:2 ,
callname:null,
contactno:02565138632,
regular:1,
bookingdetail:[
{
driverid:2,
pickupdatetime:2013-11-23 06:00:00,
from:hussainabad,
to:lyari ,
cost:2450,
journeytime:240
},
{
driverid:1,
pickupdatetime 2013-11-25 08:00:00,
from:garden,
to:def,
cost:1800,
旅途时间:30
}
],
bookingdatetime:2014-03-17T11:00:00Z
}
这是我尝试过的:
db.booking.aggregate([{$ group:{_ id:new Date($ bookingdatetime)。getMonth(),numberofbookings:{$ sum:1}}}]
并返回:
{_id : NaN,numberofbookings:3}
我哪里错了?请帮助我。
您不能在汇总管道中包含任意JavaScript,因此您正在存储 bookingdatetime
作为字符串
而不是日期
您不能使用
但是,由于您的日期字符串遵循严格的格式,您可以使用 $ substr
运算符从字符串中提取月份值:
db.test.aggregate([
{$ group:{
_id:{$ substr:['$ bookingdatetime',5,2]},
numberofbookings:{$ sum:1
}}
])
输出:
{
result:[
{
_id:03,
numberofbookings:1
},
{
_id:07,
numberofbookings b $ b},
{
_id:10,
numberofbookings:1
}
],
ok 1
}
I am writing a query in mongo db. I have a booking table and I want to get number of bookings in a month i.e. group by month. I am confused that how to get month from a date.
Here is my schema:
{
"_id" : ObjectId("5485dd6af4708669af35ffe6"),
"bookingid" : 1,
"operatorid" : 1,
"clientid" : null,
"callername" : "Sayem Hussain",
"contactno" : "0205661862",
"regular" : 0,
"bookingdetail" : [
{
"driverid" : 1,
"pickupdatetime" : "2011-04-14 11:00:00",
"from" : "white chapel",
"to" : "beach",
"cost" : 500,
"journeytime" : 60
},
{
"driverid" : 2,
"pickupdatetime" : "2012-05-14 12:00:00",
"from" : "walthamstow",
"to" : "mile end",
"cost" : 1000,
"journeytime" : 50
}
],
"bookingdatetime" : "2012-10-11T07:00:00Z"
}
{
"_id" : ObjectId("5485dd6af4708669af35ffe7"),
"bookingid" : 2,
"operatorid" : 1,
"clientid" : 1,
"callername" : null,
"contactno" : "0205561281",
"regular" : 1,
"bookingdetail" : [
{
"driverid" : 3,
"pickupdatetime" : "2012-02-12 09:00:00",
"from" : "grange park",
"to" : "queen mary",
"cost" : 650,
"journeytime" : 90
},
{
"driverid" : 2,
"pickupdatetime" : "2012-02-13 06:00:00",
"from" : "drapers bar",
"to" : "naveed restaurant",
"cost" : 1350,
"journeytime" : 120
}
],
"bookingdatetime" : "2014-07-26T05:00:00Z"
}
{
"_id" : ObjectId("5485dd6af4708669af35ffe8"),
"bookingid" : 3,
"operatorid" : 2,
"clientid" : 2,
"callername" : null,
"contactno" : "02565138632",
"regular" : 1,
"bookingdetail" : [
{
"driverid" : 2,
"pickupdatetime" : "2013-11-23 06:00:00",
"from" : "hussainabad",
"to" : "lyari",
"cost" : 2450,
"journeytime" : 240
},
{
"driverid" : 1,
"pickupdatetime" : "2013-11-25 08:00:00",
"from" : "garden",
"to" : "defence",
"cost" : 1800,
"journeytime" : 30
}
],
"bookingdatetime" : "2014-03-17T11:00:00Z"
}
And this is I have tried:
db.booking.aggregate([{$group:{_id:new Date("$bookingdatetime").getMonth(), numberofbookings:{$sum:1}}}])
and it returns:
{ "_id" : NaN, "numberofbookings" : 3 }
Where am I going wrong ? Kindly assist me.
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
}
这篇关于MongoDB - 按月分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!