Mongodb聚合、分组和计数实例

Question

我有一个文档，看起来像这样：

{
    "_id" : ObjectId("527a6b7c24a8874c078b9d10"),
    "day" : 6,
    "hour" : 15,
    "hourlyLocations" : [
        {
            "countryName" : "Spain",
            "countryCode" : "ES",
            "cityName" : "Madrid",
            "latitude" : 40,
            "longitude" : -4
        },
        {
            "countryName" : "United Kingdom",
            "countryCode" : "GB",
            "cityName" : "Soest",
            "latitude" : 51.5,
            "longitude" : -0.13
        }
    ],
    "minute" : 18,
    "month" : 11,
    "year" : 2013
}

"hourlyLocations“是一系列嵌入式文档(为简洁起见，此处只显示了两个)。

我正在尝试运行一个聚合，它将返回每个国家，该国家的所有城市(一次)和每个城市的实例数量。

这是我到目前为止所得到的：

db.hourly.aggregate(
[
    { "$project" : { "hourly" : "$hourlyLocations" } },
    { "$unwind" : "$hourly" },
    { "$group" : { "_id" : { "country" : "$hourly.countryName" }, "city" : { "$push" : "$hourly.cityName" } } },
]
)

这将返回类似以下内容：

{
        "_id" : {
            "country" : "Italy"
        },
        "city" : [
            "Manzano",
            "Cologno Monzese",
            "Rome",
            "Manzano",
            "Cologno Monzese",
            "Venice",
            "Milan",
            "Rome",
            "Milan",
            "Manzano",
            "Cologno Monzese",
            "Venice",
            "Milan",
            "Rome",
            "Milan",
            "Manzano",
            "Cologno Monzese",
            "Venice",
            "Milan",
            "Rome",
            "Manzano",
            "Cologno Monzese",
            "Venice",
            "Milan",
            "Casalnuovo di Napoli",
            "Manzano",
            "Cologno Monzese",
            "Venice",
            "Milan",
            "Casalnuovo di Napoli",
            "Milan"
        ]
    }

所以我得到了所有城市按城市分组的所有实例。我现在要做的是对每个城市的实例数进行分组和计数。如下所示：

{
        "_id" : {
            "country" : "Italy"
        },
        "city" : [
            "Casalnuovo di Napoli" : "12"
            "Cologno Monzese" : "10",
            "Manzano" : "9",
            "Milan" : "6",
            "Rome" : "3",
            "Venice" : "1"
        ]
    }

我已经尝试了一些方法，但都不能正确地完成。我如何才能获得我所要求的每个国家的每个城市的数量？

非常感谢，

尼克。

Answer 1

尝试：

db.hourly.aggregate(
[
    { "$project" : { "hourly" : "$hourlyLocations" } },
    { "$unwind" : "$hourly" },
    { $group: { _id: { country: "$hourly.countryName", city: "$hourly.cityName" }, count: { $sum: 1 } } },
    { $sort: { count: -1 } },
    {  $group: { _id: "$_id.country", cities: { $push: { city: "$_id.city", count: "$count"  } }  } }
]
)

它不完全是所需的结构。取而代之的是：

{
    "_id" : {
        "country" : "Italy"
    },
    "cities" : [
        { "city": "Cologno Monzese", "count": 12},
        { "city": "Milan", "count": 6},
        { "city": "Rome", "count": 3},
    ]
}