mongodbmongoosemaxby

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

Solution

  • 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