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 by country, state and city in MongoDB, use the $group stage with $addToSet to organize data hierarchically.

Syntax

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

Sample Data

db.demo620.insertMany([
    {"Country": "IND", "City": "Delhi", "state": "Delhi"},
    {"Country": "IND", "City": "Bangalore", "state": "Karnataka"},
    {"Country": "IND", "City": "Mumbai", "state": "Maharashtra"}
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("5e9a8de96c954c74be91e6a1"),
        ObjectId("5e9a8e336c954c74be91e6a3"),
        ObjectId("5e9a8e636c954c74be91e6a4")
    ]
}

Display all documents from the collection ?

db.demo620.find();

{ "_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" }

Example: Hierarchical Grouping

Aggregate documents by country, state and city using a two-stage pipeline ?

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

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

How It Works

• Stage 1: Groups by country and state, collecting cities using $addToSet
• Stage 2: Groups by country only, nesting states and their cities into arrays
• $addToSet ensures unique values and prevents duplicates

Conclusion

Use a two-stage aggregation pipeline with $group and $addToSet to create hierarchical data structures grouped by country, state, and city. This approach organizes geographical data into nested arrays for easy analysis.