MongoDB Aggregate: เรียงลำดับ Subdocument ตามผลรวม Salary

สร้างคอลเลกชันตัวอย่าง (โครงสร้างซ้อน 3 ระดับ)

> db.demo136.insertOne(
  {
    "Name": "Chris",
    "Details": [
      {
        "Id": "101",
        "EmployeeName": "Mike",
        "EmployeeDetails": [
          {"EmpId": 1001, "Salary": 1000},
          {"EmpId": 1002, "Salary": 2000}
        ]
      },
      {
        "Id": "102",
        "EmployeeName": "David",
        "EmployeeDetails": [
          {"EmpId": 1004, "Salary": 4000},
          {"EmpId": 1005, "Salary": 5000}
        ]
      }
    ]
  }
);
{ "acknowledged": true, "insertedId": ObjectId("5e31b15cfdf09dd6d085399f") }

แสดงข้อมูล

> db.demo136.find();

{ "_id": ObjectId("5e31b15cfdf09dd6d085399f"), "Name": "Chris", "Details": [ { "Id": "101", "EmployeeName": "Mike", "EmployeeDetails": [ { "EmpId": 1001, "Salary": 1000 }, { "EmpId": 1002, "Salary": 2000 } ] }, { "Id": "102", "EmployeeName": "David", "EmployeeDetails": [ { "EmpId": 1004, "Salary": 4000 }, { "EmpId": 1005, "Salary": 5000 } ] } ] }

คิวรี Aggregate: $unwind Details -> $project คำนวณ SalaryTotal -> $sort -> $group รวมกลับ

> db.demo136.aggregate([
  { $unwind: "$Details" },
  {
    $project: {
      Name: 1,
      Details: 1,
      SalaryTotal: { $sum: "$Details.EmployeeDetails.Salary" }
    }
  },
  { $sort: { SalaryTotal: -1 } },
  {
    $group: {
      _id: "$_id",
      Name: { $first: "$Name" },
      Details: { $push: "$Details" }
    }
  }
]).pretty()

ผลลัพธ์: Details เรียงจาก SalaryTotal มากไปน้อย (David ก่อน Mike)

{ "_id": ObjectId("5e31b15cfdf09dd6d085399f"), "Name": "Chris", "Details": [ { "Id": "102", "EmployeeName": "David", "EmployeeDetails": [ { "EmpId": 1004, "Salary": 4000 }, { "EmpId": 1005, "Salary": 5000 } ] }, { "Id": "101", "EmployeeName": "Mike", "EmployeeDetails": [ { "EmpId": 1001, "Salary": 1000 }, { "EmpId": 1002, "Salary": 2000 } ] } ] }