我在mongo db中编写查询。我有一个预订表,我想获得一个月内的预订数量,即按月分组。我很困惑如何从日期中获取月份。

这是我的架构:

{
    "_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"
}

这是我尝试过的:
db.booking.aggregate([{$group:{_id:new Date("$bookingdatetime").getMonth(), numberofbookings:{$sum:1}}}])

它返回:
{ "_id" : NaN, "numberofbookings" : 3 }

我要去哪里错了?请帮助我。

最佳答案

您不能在聚合管道中包含任意JavaScript,因此,由于您将bookingdatetime存储为string而不是Date,因此不能使用$month运算符。

但是,由于日期字符串遵循严格的格式,因此可以使用 $substr 运算符从字符串中提取月份值:

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

输出:

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

关于MongoDB-按月分组,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/27366209/

10-11 08:40