Aggregate by country, state and city in a MongoDB collection with multiple documents

Aggregation operations group values from multiple documents together, and can perform a variety of operations on the grouped data to return a single result.

To aggregate in MongoDB, use aggregate(). Let us create a collection with documents −

> db.demo620.insertOne({"Country":"IND","City":"Delhi",state:"Delhi"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5e9a8de96c954c74be91e6a1") } > db.demo620.insertOne({"Country":"IND","City":"Bangalore",state:"Karnataka"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5e9a8e336c954c74be91e6a3") } > db.demo620.insertOne({"Country":"IND","City":"Mumbai",state:"Maharashtra"}); {    "acknowledged" : true,    "insertedId" : ObjectId("5e9a8e636c954c74be91e6a4") }

Display all documents from a collection with the help of find() method −

> db.demo620.find();

This will produce the following output −

{ "_id" : ObjectId("5e9a8de96c954c74be91e6a1"), "Country" : "IND", "City" : "Delhi", "state" : "Delhi" } { "_id" : ObjectId("5e9a8e336c954c74be91e6a3"), "Country" : "IND", "City" : "Bangalore", "state" : "Karnataka" } { "_id" : ObjectId("5e9a8e636c954c74be91e6a4"), "Country" : "IND", "City" : "Mumbai", "state" : "Maharashtra" }

Following is the query to aggregate by country, state and city −

> db.demo620.aggregate([ ...    { "$group": { ...       "_id": { ...          "Country": "$Country", ...          "state": "$state" ...       }, ...       "City": { ...          "$addToSet": { ...             "City": "$City" ...          } ...       } ...    }}, ...    { "$group": { ...       "_id": "$_id.Country", ...       "states": { ...          "$addToSet": { ...             "state": "$_id.state", ...             "City": "$City" ...          } ...       } ...    }} ... ]).pretty();

This will produce the following output −

{    "_id" : "IND",    "states" : [       {          "state" : "Delhi",          "City" : [             {                "City" : "Delhi"             }          ]       },       {          "state" : "Maharashtra",          "City" : [             {                   "City" : "Mumbai"             }          ]       },       {          "state" : "Karnataka",          "City" : [             {                "City" : "Bangalore"             }          ]       }    ] }