CODEWITHSUNDEEP
mongodbmongoosemaxby
saif
saif

Reputation: 487

Select top n items from each array order by desc

Consider a collection with the following documents:

{
    "_id" : 1,
    "name" : "Class 1",
    "students" : [  
        { "rollNo" : 10001, "name" : "Ram", "score" : 65 },
        { "rollNo" : 10002, "name" : "Shyam", "score" : 90 }, <=
        { "rollNo" : 10003, "name" : "Mohan", "score" : 75 }  <=
    ]
},
{
    "_id" : 2,
    "name" : "Class 2",
    "students" : [  
        { "rollNo" : 20001, "name" : "Krishna", "score" : 88 },
        { "rollNo" : 20002, "name" : "Sohan", "score" : 91 }, <=
        { "rollNo" : 20003, "name" : "Radhika", "score" : 82 },
        { "rollNo" : 20004, "name" : "Komal", "score" : 55 },
        { "rollNo" : 20005, "name" : "Sonam", "score" : 91 }  <=        
    ]
},
{
    "_id" : 3,
    "name" : "Class 3",
    "students" : [  
        { "rollNo" : 30001, "name" : "Monika", "score" : 77 }, <=
        { "rollNo" : 30002, "name" : "Rahul", "score" : 81 }   <=
    ]
}

My objective is to get top N students from each array ordered by descending score (consider top 2 students order by score).

My expected result is:

enter image description here

Upvotes: 1

Views: 936

Answers (2)

Xavier Guihot
Xavier Guihot

Reputation: 61666

Starting in Mongo 5.2, it's a nice use case for the new $sortArray and $firstN aggregation operators:

// { students: [
//    { name: "Ram",   score: 65 },
//    { name: "Shyam", score: 90 },
//    { name: "Mohan", score: 75 }
// ]}
// { students: [
//    { name: "Krishna", score: 88 },
//    { name: "Sohan",   score: 91 },
//    { name: "Radhika", score: 82 },
//    { name: "Komal",   score: 55 },
//    { name: "Sonam",   score: 91 }
// ]}
// { students: [
//    { name: "Monika", score: 77 },
//    { name: "Rahul",  score: 81 }
// ]}
db.collection.aggregate(
  { $set: { students: {
    $firstN: {
      n: 2,
      input: { $sortArray: { input: "$students", sortBy: { score: -1 } } }
    }
  }}}
)
// { students: [
//    { name: "Shyam", score: 90 },
//    { name: "Mohan", score: 75 }
// ]}
// { students: [
//    { name: "Sohan", score: 91 },
//    { name: "Sonam", score: 91 }
// ]}
// { students: [
//    { name: "Rahul", score: 81 },
//    { name: "Monika", score: 77 }
// ]}

This:

  • sorts ($sortArray) each students array by score
  • in order to get the first 2 ($firstN) students

Upvotes: 0

Akrion
Akrion

Reputation: 18515

If you just want the student records without the grouping you can simply $unwind and then $sort:

db.collection.aggregate([
  { $unwind: "$students" },
  { $sort: { "students.score": -1 } },
  { $limit: 2 }
])

That would leave however the students object. For cleaner output you could use $replaceRoot with $mergeObjects:

db.collection.aggregate([
  { $unwind: "$students" },
  { $sort: { "students.score": -1 } },
  { $replaceRoot: {
      newRoot: {
        $mergeObjects: [ { _id: "$_id", class: "$name" }, "$students" ]
      }
    }
  },
  { $limit: 2 }
])

See it working here

This would give you the following output:

[
  {
    "_id": 2,
    "class": "Class 2",
    "name": "Sonam",
    "rollNo": 20005,
    "score": 91
  },
  {
    "_id": 2,
    "class": "Class 2",
    "name": "Sohan",
    "rollNo": 20002,
    "score": 91
  }
]

UPDATE:

Use this to get top 2 for each group:

db.collection.aggregate([
  { $unwind: "$students" },
  { $sort: { "students.score": -1 }
  },
  {
    $group: {
      "_id": "$_id",
      "name": { $first: "$name" },
      "students": { $push: "$students" }
    }
  },
  {
    "$project": {
      "top_two": { "$slice": [ "$students", 2 ] }
    }
  }
])

See it working here

Upvotes: 2

Related Questions