Mongodb汇总，分组和计数实例 [英] Mongodb aggregate, group and count instances

问题描述

我有一个看起来像这样的文件:

I have a document which looks like this:

{
    "_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"是一系列嵌入式文档(为简便起见，此处仅显示两个).

"hourlyLocations" is a series of embedded documents (just two shown here for brevity).

我正在尝试进行汇总，以返回每个国家/地区，该国家/地区的所有城市(一次)以及每个城市的实例数.

I'm trying to run an aggregation which will return each country, all the cities in that country (once) and the number of instances of each city.

这是到目前为止我得到的:

Here's what I've got so far:

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"
        ]
    }

因此，我具有按城市分组的所有城市的所有实例.我现在想做的是对每个城市的实例数量进行分组和计数.像这样:

So I've got all the instances of all the cities grouped by city. What I want to do now is to group by, and count, the number of instances of each city. Something like this:

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

我尝试了一些尝试，但未能正确解决.如何根据需要获取每个国家/地区的每个城市的计数?

I've tried a few things but haven't been able to get it right. How can I get the count of each city per country as I require?

非常感谢，

尼克.

推荐答案

尝试:

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"  } }  } }
]
)

这不是所要求的结构.相反，您得到:

It's not quite the requested structure. Instead you get:

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

这篇关于Mongodb汇总，分组和计数实例的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！