Group across two columns in MongoDB?

To group across two columns in MongoDB, use the $lookup operator to join documents based on matching field values, then perform aggregation operations on the combined data.

Syntax

db.collection.aggregate([
    {
        "$lookup": {
            "from": "collection_name",
            "localField": "field1",
            "foreignField": "field2",
            "as": "joined_data"
        }
    },
    { "$unwind": "$joined_data" },
    {
        "$project": {
            "field": 1,
            "result": { "$sum": ["$value1", "$joined_data.value2"] }
        }
    }
]);

Sample Data

db.demo132.insertMany([
    {"CountryName1": "US", "CountryName2": "UK", "Value": 50},
    {"CountryName1": "UK", "CountryName2": "AUS", "Value": 10},
    {"CountryName1": "AUS", "CountryName2": "US", "Value": 40}
]);

Display all documents from the collection ?

db.demo132.find();

{ "_id": ObjectId("..."), "CountryName1": "US", "CountryName2": "UK", "Value": 50 }
{ "_id": ObjectId("..."), "CountryName1": "UK", "CountryName2": "AUS", "Value": 10 }
{ "_id": ObjectId("..."), "CountryName1": "AUS", "CountryName2": "US", "Value": 40 }

Example: Group and Sum Values Across Two Columns

Join documents where CountryName1 matches CountryName2 and sum their values ?

db.demo132.aggregate([
    {
        "$lookup": {
            "from": "demo132",
            "localField": "CountryName1",
            "foreignField": "CountryName2",
            "as": "out"
        }
    },
    {
        "$unwind": "$out"
    },
    {
        "$project": {
            "_id": 0,
            "CountryName1": 1,
            "total": { "$sum": ["$Value", "$out.Value"] }
        }
    }
]);

{ "CountryName1": "US", "total": 90 }
{ "CountryName1": "UK", "total": 60 }
{ "CountryName1": "AUS", "total": 50 }

How It Works

• $lookup: Joins documents where CountryName1 equals CountryName2 from the same collection
• $unwind: Flattens the joined array to work with individual matched documents
• $project: Calculates the sum of Value fields from both original and joined documents

Conclusion

Use $lookup with $unwind and $project to group and aggregate data across two columns. This approach enables cross-column joins and calculations within the same collection.