Group across two columns in MongoDB?

To group across two columns, use $lookup. Let us create a collection with documents −

> db.demo132.insertOne({"CountryName1":"US","CountryName2":"UK",Value:50}); {    "acknowledged" : true,    "insertedId" : ObjectId("5e31950468e7f832db1a7f75") } > db.demo132.insertOne({"CountryName1":"UK","CountryName2":"AUS",Value:10}); {    "acknowledged" : true,    "insertedId" : ObjectId("5e31951d68e7f832db1a7f76") } > db.demo132.insertOne({"CountryName1":"AUS","CountryName2":"US",Value:40}); {    "acknowledged" : true,    "insertedId" : ObjectId("5e31952c68e7f832db1a7f77") }

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

> db.demo132.find();

This will produce the following output −

{ "_id" : ObjectId("5e31950468e7f832db1a7f75"), "CountryName1" : "US", "CountryName2" : "UK", "Value" : 50 } { "_id" : ObjectId("5e31951d68e7f832db1a7f76"), "CountryName1" : "UK", "CountryName2" : "AUS", "Value" : 10 } { "_id" : ObjectId("5e31952c68e7f832db1a7f77"), "CountryName1" : "AUS", "CountryName2" : "US", "Value" : 40 }

Following is the query to group across two columns in MongoDB −

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

This will produce the following output −

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