按内部数组分组的MongoDB聚合计数

Question

我使用的是MongoDB 3.2。

在这个问题上遇到问题。。。

（为类似SQL的语法提前道歉，我只是从SQL学习Mongo）

我需要找到文件的数量

"audit_event_type_id" : 1
shop2.date" BETWEEN "2014-10-01" AND "2015-09-01"

按类别分组

shop2.facility_name
MONTH(shop2.date)
YEAR(shop2.date)

订购人

count DESC

有没有办法将查询中的字符串转换为日期？我知道我在存储shop2。当日期应为ISODate时，将其作为字符串。如果不是，我们就假设它是一个ISODate。

下面是一个文档示例：

   {
        "_id" : ObjectId("56b38f31967a337c432119cb"),
        "audit_event_id" : 12382306,
        "audit_event_type_id" : 2,
        "group_id" : 3333489,
        "applicant_id" : 3428508,
        "service_credit_id" : 3804844,
        "page_hit_id" : 43870954,
        "shop1" : {
            "facility_id" : 28,
            "facility_name" : "Fake1",
            "date" : "2014-08-13",
            "time" : "07:00",
            "expedite" : false,
            "collect_biometrics" : false,
            "block_id" : 364814,
            "appointment_category" : "Renewal"
        },
        "shop2" : {
            "facility_id" : 29,
            "facility_name" : "Fake2",
            "date" : "2014-08-07",
            "time" : "07:00",
            "expedite" : false,
            "block_id" : 373614,
            "appointment_category" : "Biometrics"
        },
        "created_at" : "2014-07-30 00:44:36",
        "updated_at" : "2014-07-30 00:44:36",
        "user_id" : 3242890,
        "payment_id" : null,
        "mission_id" : 24,
        "affected_user_id" : null
    }

任何帮助都将不胜感激。谢谢

我已经更新了所有的shop1。日期

   {
        "_id" : ObjectId("56b38f31967a337c432119cb"),
        "audit_event_id" : 12382306,
        "audit_event_type_id" : 2,
        "group_id" : 3333489,
        "applicant_id" : 3428508,
        "service_credit_id" : 3804844,
        "page_hit_id" : 43870954,
        "shop1" : {
            "facility_id" : 28,
            "facility_name" : "Fake1",
            "date" : ISODate("2014-08-13T00:00:00Z"),
            "time" : "07:00",
            "expedite" : false,
            "collect_biometrics" : false,
            "block_id" : 364814,
            "appointment_category" : "Renewal"
        },
        "shop2" : {
            "facility_id" : 29,
            "facility_name" : "Fake2",
            "date" : ISODate("2014-08-07T00:00:00Z"),
            "time" : "07:00",
            "expedite" : false,
            "block_id" : 373614,
            "appointment_category" : "Biometrics"
        },
        "created_at" : "2014-07-30 00:44:36",
        "updated_at" : "2014-07-30 00:44:36",
        "user_id" : 3242890,
        "payment_id" : null,
        "mission_id" : 24,
        "affected_user_id" : null
    }

Answer 1

您可以使用mongodb中的聚合框架来实现以下目的：

db.Collection.aggregate([
{ $match: { 
    audit_event_type_id: 1,
    "shop2.date": { $gte: ISODate("2014-10-01T00:00:00.000Z"), $lt: ISODate("2015-09-01T00:00:00.000Z") } } 
},
{ $group : {
        _id : { "shop2.facility_name": "$shop2.facility_name", month: { $month: "$shop2.date" }, year: { $year: "$shop2.date" } },
        count: { $sum: 1 } }
},
{ $sort : { count: -1 } }
])