MongoDB Aggregation

Question

I have a the following collection

{ "data" :[

   { "country" :"USA", "state": "NJ", "County" : "Middlesex", "city" : "Edison",  "population" :0923422 },

   { "country" :"USA", "state": "NJ", "County" : "Mercer", "city" : "Princeton",  "population" :0923422 },
   { "country" :"USA", "state": "NY", "County" : "Middlesex", "city" : "Edison",  "population" :234234 },
   { "country" :"USA", "state": "NY", "County" : "Ocean", "city" : "Albony",  "population" :234324 },
   { "country" :"USA", "state": "PA", "County" : "Central", "city" : "Edison",  "population" :0923422 },
   { "country" :"USA", "state": "NJ", "County" : "Middlesex", "city" : "Edison",  "population" :23432 },
   { "country" :"USA", "state": "OH", "County" : "Middlesex", "city" : "Cincinatti",  "population" :0923422 },
   { "country" :"USA", "state": "OH", "County" : "Middlesex", "city" : "Columbos",  "population" :23432 } ]

}

looking to find the total population by Country, State, County and City with nested aggregation in mongodb.

Please help with query.

Answer 1

Query:-

db.collection.aggregate([
{$group:
         {
           _id : {"country" : "$country",
               "state" : "$state",
               "County" : "$County",
               "city" : "$city"
           },
            "totalPopulationOfCity" : {$sum: "$population" }

         }},
{$group:
         {
           _id : {"country" : "$_id.country",
               "state" : "$_id.state",
               "County" : "$_id.County"               
           },            
            "cities" : { $addToSet:   {"city" : "$_id.city", "totalPopulationOfCity" : "$totalPopulationOfCity"}  }  ,
            "totalPopulationOfCounty" : {$sum: "$totalPopulationOfCity" }

         }},         
{$group:
         {
           _id : {"country" : "$_id.country",
               "state" : "$_id.state"

           },            

            "counties" : {$addToSet: {"County": "$_id.County", "cities" : "$cities", 
                "totalPopulationOfCounty" : "$totalPopulationOfCounty"} } ,
            "totalPopulationOfState" : {$sum: "$totalPopulationOfCounty" }            
         }},         

{$group:
         {
           _id : {"country" : "$_id.country"                                      
           },            
            "states" : {$addToSet : {"state" : "$_id.state", "counties": "$counties", "totalPopulationOfState" : "$totalPopulationOfState"}},                            
            "totalPopulationOfCountry" : {$sum: "$totalPopulationOfState" }            
         }},                  

]);

Output:-

/* 1 */
{
    "_id" : {
        "country" : "USA"
    },
    "states" : [ 
        {
            "state" : "NY",
            "counties" : [ 
                {
                    "County" : "Ocean",
                    "cities" : [ 
                        {
                            "city" : "Albony",
                            "totalPopulationOfCity" : 234324
                        }
                    ],
                    "totalPopulationOfCounty" : 234324
                }, 
                {
                    "County" : "Middlesex",
                    "cities" : [ 
                        {
                            "city" : "Edison",
                            "totalPopulationOfCity" : 234234
                        }
                    ],
                    "totalPopulationOfCounty" : 234234
                }
            ],
            "totalPopulationOfState" : 468558
        }, 
        {
            "state" : "NJ",
            "counties" : [ 
                {
                    "County" : "Mercer",
                    "cities" : [ 
                        {
                            "city" : "Princeton",
                            "totalPopulationOfCity" : 923422
                        }
                    ],
                    "totalPopulationOfCounty" : 923422
                }, 
                {
                    "County" : "Middlesex",
                    "cities" : [ 
                        {
                            "city" : "Edison",
                            "totalPopulationOfCity" : 946854
                        }
                    ],
                    "totalPopulationOfCounty" : 946854
                }
            ],
            "totalPopulationOfState" : 1870276
        }, 
        {
            "state" : "OH",
            "counties" : [ 
                {
                    "County" : "Middlesex",
                    "cities" : [ 
                        {
                            "city" : "Cincinatti",
                            "totalPopulationOfCity" : 923422
                        }, 
                        {
                            "city" : "Columbos",
                            "totalPopulationOfCity" : 23432
                        }
                    ],
                    "totalPopulationOfCounty" : 946854
                }
            ],
            "totalPopulationOfState" : 946854
        }, 
        {
            "state" : "PA",
            "counties" : [ 
                {
                    "County" : "Central",
                    "cities" : [ 
                        {
                            "city" : "Edison",
                            "totalPopulationOfCity" : 923422
                        }
                    ],
                    "totalPopulationOfCounty" : 923422
                }
            ],
            "totalPopulationOfState" : 923422
        }
    ],
    "totalPopulationOfCountry" : 4209110
}

Verification using excel:-