MongoDB 3-level aggregation

Question

Suppose we have a list of schools so that each school is assigned city, state (province) and country.

Is it possible to build an aggregated list of unique cities/states/countries with MongoDB aggregation framework?

What I have:

[
{
    "School_name” : "School #1",
    "state" : "CA"
    "city” : "San-Francisco",
    "country” : "USA",
},
{
    "School_name” : "School #2",
    "state" : "CA"
    "city” : "San-Francisco",
    "country” : "USA",
},
{
    "School_name” : "School #3",
    "state" : "CA"
    "city” : "Los Angeles",
    "country” : "USA",
},
{
    "School_name” : "School #4",
    "state" : "CO"
    "city” : "Denver",
    "country” : "USA",
},
{
    "School_name” : "School #5",
    "state" : "CO"
    "city” : "Boulder",
    "country” : "USA",
},
{
    "School_name” : "School #6",
    "state" : "Hessen"
    "city” : "Frankfurt",
    "country” : "Germany",
}

]

What I need to get:

[
{
 "country" : "Germany",
     "states" :  
  [     
      {
    "state" : "Hessen",
        "cities" : [Frankfurt,...]
      }
      ]
},
{
 "country" : "USA",
     "states" :  
      [
    {
    "state" : "CA",
        "cities" : [San-Francisco, Los Angeles, ...]
    }
    {
    "state" : "CO",
        "cities" : [Boulder, Denver, ...]
    }
      ]
},

]

Answer 1

Keeping the stages simple. And no need to $addToSet since the top level group sorts out duplicates:

db.school.aggregate([{
    $group: { 
        _id: { 
            country: "$country",
            state: "$state", 
            city: "$city" 
        }
    }},
   {$group: { 
       _id: { 
       country: "$_id.country",
       state: "$_id.state" },
       cities: {$push: "$_id.city"} }
   },
   {$group: { 
       _id: "$_id.country",
       states: {$push: { state: "$_id.state", cities: "$cities" }} 
   }},
   {$project: { _id: 0, country: "$_id", states: "$states" }}
])

So it is actually a three group stage aggregation

Answer 2

Try this:

db.collection.aggregate([
{$group:{_id:{country:"$country", state:"$state"}, cities:{$addToSet:"$city"}}}, 
{$project:{country:"$_id.country", state:"$_id.state", cities:1, _id:0}}, 
{$group:{_id:"$country", states:{$push:{state:"$state", cities:"$cities"}}}}
{$project:{country:"$_id",states:1, _id:0}}
])