CODEWITHSUNDEEP
mongodbaggregation-framework
ciso
ciso

Reputation: 3050

How to use aggregation pipeline to sort by two levels of grouping?

Given this data: 

[ {country:"US", city:"NY", cnt: 10}
  {country:"IT", city:"MI", cnt:  9}
  {country:"US", city:"LA", cnt:  8}
  {country:"IT", city:"RM", cnt: 20} ]

Is there a way using the mongoDB aggregation pipeline to create a result array which looks like this (not based on alpha codes, just the cnt):

[ {country:"IT", city:"RM", cnt:20}
  {country:"IT", city:"MI", cnt: 9}
  {country:"US", city:"NY", cnt:10}
  {country:"US", city:"LA", cnt: 8} ]

}

In other words, an array sorted (descending) by the country with the highest total and then by each city's highest total?

I can group by country or group by country and city, but neither will give me the above result. One gives me two lines with totals for each country, the other gives me four lines with country city totals, but not sorted by the country with the highest totals.

Upvotes: 0

Views: 326

Answers (1)

Blakes Seven
Blakes Seven

Reputation: 50416

Just add $sort after the $group:

{ "$sort": { "country": 1, "cnt": -1  } }

Results in:

{ "country" : "IT", "city" : "RM", "cnt" : 20 }
{ "country" : "IT", "city" : "MI", "cnt" : 9 }
{ "country" : "US", "city" : "NY", "cnt" : 10 }
{ "country" : "US", "city" : "LA", "cnt" : 8 }

To be use totals then group to get the total count:

{ "$group": {
  "_id": "$country",
  "cities": { "$push": {
    "city": "$city",
    "cnt": "$cnt"
  }},
  "totalCount": { "$sum": "$cnt" }
}},
{ "$unwind": "$cities" },
{ "$sort": { "totalCount": -1, "_id": 1, "cities.cnt": -1 }},
{ "$project": {
  "_id": 0,
  "country": "$_id",
  "city": "$cities.city",
  "cnt": "$cities.cnt"
}}

Project out to get the same result

Upvotes: 1

Related Questions