ดึงเอกสาร MongoDB ที่มีค่าสูงสุดต่อกลุ่มในคอลเลกชัน

ใช้ $sort ร่วมกับ $group ใน Aggregate Framework เพื่อดึงเอกสารที่มีค่าสูงสุดต่อกลุ่ม

ตัวอย่างการใช้งาน

สร้างคอลเลกชัน maxAttributePerGroup:

> db.maxAttributePerGroup.insertOne({"StudentFirstName":"John","StudentLastName":"Smith","StudentAge":29,"StudentId":10});
{ "acknowledged" : true, "insertedId" : ObjectId("5c76ee341e9c5dd6f1f78277") }
> db.maxAttributePerGroup.insertOne({"StudentFirstName":"Carol","StudentLastName":"Taylor","StudentAge":19,"StudentId":10});
{ "acknowledged" : true, "insertedId" : ObjectId("5c76ee4e1e9c5dd6f1f78278") }
> db.maxAttributePerGroup.insertOne({"StudentFirstName":"Adam","StudentLastName":"Smith","StudentAge":34,"StudentId":20});
{ "acknowledged" : true, "insertedId" : ObjectId("5c76ee631e9c5dd6f1f78279") }
> db.maxAttributePerGroup.insertOne({"StudentFirstName":"Bob","StudentLastName":"Taylor","StudentAge":58,"StudentId":20});
{ "acknowledged" : true, "insertedId" : ObjectId("5c76ee791e9c5dd6f1f7827a") }

แสดงข้อมูลทั้งหมด:

> db.maxAttributePerGroup.find().pretty();

{"_id" : ObjectId("5c76ee341e9c5dd6f1f78277"), "StudentFirstName" : "John", "StudentLastName" : "Smith", "StudentAge" : 29, "StudentId" : 10 }
{"_id" : ObjectId("5c76ee4e1e9c5dd6f1f78278"), "StudentFirstName" : "Carol", "StudentLastName" : "Taylor", "StudentAge" : 19, "StudentId" : 10 }
{"_id" : ObjectId("5c76ee631e9c5dd6f1f78279"), "StudentFirstName" : "Adam", "StudentLastName" : "Smith", "StudentAge" : 34, "StudentId" : 20 }
{"_id" : ObjectId("5c76ee791e9c5dd6f1f7827a"), "StudentFirstName" : "Bob", "StudentLastName" : "Taylor", "StudentAge" : 58, "StudentId" : 20 }

คำสั่ง Aggregate เพื่อดึงเอกสารที่มีอายุมากที่สุดต่อกลุ่ม StudentId:

> db.maxAttributePerGroup.aggregate([
... {$sort:{"StudentId":1,"StudentAge":-1}},
... {$group:{
... "_id":"$StudentId",
... "StudentAge":{"$first":"$StudentAge"},
... "StudentFirstName":{"$first":"$StudentFirstName"},
... "StudentLastName":{"$first":"$StudentLastName"}
... }}
... ]).pretty();

{"_id" : 20, "StudentAge" : 58, "StudentFirstName" : "Bob", "StudentLastName" : "Taylor" }
{"_id" : 10, "StudentAge" : 29, "StudentFirstName" : "John", "StudentLastName" : "Smith" }