MongoDB: จัดกลุ่มข้อมูลข้ามสองคอลัมน์ด้วย $lookup

สร้างคอลเลกชันตัวอย่าง

ข้อมูลประเทศคู่คู่พร้อมค่า Value:

> 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") }

แสดงข้อมูล

> db.demo132.find();

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

คิวรี Aggregate: $lookup self-join

จอยน์คอลเลกชันตัวเอง ผูก CountryName1 กับ CountryName2 แล้วรวม Value:

> 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 }