How to filter by count on a list filter

[mar...@letterboxes.org]

I wasn't even sure how to phrase the subject, but I'll give you an
example. Say I have a "state" collection similar to this:


{
name: 'Texas'
capital: 'Austin',
cities: [
{name: 'Dallas', population: 1241162},
{name: 'San Antonio', population: 1382951}
]
},
{
name: 'North Carolina',
capital: 'Raleigh',
cities: [...]
}
[....]



What I've been able to do so far is query on states that have cities
with a population of at least, say 500,000:

db.states.find({cities.population': {$gte: 500000}})

But what I *really* want to do is filter on states that have at least 3
cities with a population of at least 500,000. But try as I might, I
can't figure out the query. Perhaps this is not the ideal "schema" for
the data?

Bonus points if the query would only return the list of cities with at
least 500,000 instead of all of the cities in that state.

[Will Berkeley]

Hi. Use the aggregation framework:

db.states.aggregate([
    { "$unwind": "$cities" },
    { "$match": { "cities.population": { "$gte": 500000 } } },
    { "$group": { "_id": "$name", "cities": { "$push": "$cities" }, "count": { "$inc" : 1 } } }
    { "$match": { "count" : { "gte" : 3 } } }
])

This both finds all states with >= 3 cities of a half million people or more and returns those states with only cities of 500,000 or more people in the cities array.

-Will

[mar...@letterboxes.org]

On Fri, Jul 11, 2014, at 10:17 PM, Will Berkeley wrote:
> Hi. Use the aggregation framework

> <http://docs.mongodb.org/manual/core/aggregation-introduction/>:

>
> db.states.aggregate([
> { "$unwind": "$cities" },
> { "$match": { "cities.population": { "$gte": 500000 } } },
> { "$group": { "_id": "$name", "cities": { "$push": "$cities" },
> "count":
> { "$inc" : 1 } } }
> { "$match": { "count" : { "gte" : 3 } } }
> ])
>
> This both finds all states with >= 3 cities of a half million people or
> more and returns those states with only cities of 500,000 or more people
> in
> the cities array.
>

Thanks! And by studying your example I much better understand the
aggregation framework ( I think you meant "$sum" instead of "$inc"
though.).

[Will Berkeley]

Apologies! I must've changed it by accident when formatting the code for the post.

-Will