I have a student collection that looks like this: How to find a maximum of two exam scores for every student

[
  {
    "_id" : ObjectId("61868aa03b2fe72b58c891a5"),
    "name" : "Max",
    "examScores" : [ 
        {
            "difficulty" : 4,
            "score" : 57.9
        }, 
        {
            "difficulty" : 6,
            "score" : 62.1
        }, 
        {
            "difficulty" : 3,
            "score" : 88.5
        }
    ]
},

{
    "_id" : ObjectId("61868aa03b2fe72b58c891a6"),
    "name" : "Manu",
    "examScores" : [ 
        {
            "difficulty" : 7,
            "score" : 52.1
        }, 
        {
            "difficulty" : 2,
            "score" : 74.3
        }, 
        {
            "difficulty" : 5,
            "score" : 53.1
        }
    ]
}
]

I want to use aggregation to return the two highest examScores.score like this:

[
 {
  name: "Max",
  maxExams: [88.5, 62.1]
 },
 {
  name: "Manu",
  maxExams: [74.3, 53.1]
 }
]

I am tried the aggregation stage $project and $unwind and $sort but all of them fail to solve my problem

CodePudding user response：

Here it is:

 mongos> db.s.aggregate([ 
        {$unwind:"$examScores"},
        {$sort:{"name":1,"examScores.score":-1}},
        {$group:{ _id:"$name" ,Scores:{$push:"$examScores.score"}  }}, 
        {$project:{_id:0,name:"$_id",maxExam:{$slice:["$Scores",2]}}}
        ])

       { "name" : "Manu", "maxExam" : [ 74.3, 53.1 ] }
       { "name" : "Max", "maxExam" : [ 88.5, 62.1 ] }
mongos> 

Explained:

1. Unwind the scores so you can sort them later.
2. Sort by name and scores max to min
3. group the scores in new array Scores
4. project the name & maxExam ( sliced first 2 from Scores )

CodePudding user response：

Query

• you can reduce and keep only the max 2 scores
• starts with [-1,-1] if score >= first member add left(old right will be removed), else if score >= second member add right(old right will be removed), else do nothing
• if you wanted more than 2-3
  • if MongoDB 5 you $setWindowFields could be used
  • else unwind group slice 2 solution

*here you dont need unwind/group/sort etc because only 2.

Test code here

aggregate(
[{"$project": 
    {"_id": 0,
      "name": 1,
      "maxExams": 
      {"$reduce": 
        {"input": "$examScores",
          "initialValue": [-1, -1],
          "in": 
          {"$switch": 
            {"branches": 
              [{"case": 
                  {"$gte": ["$$this.score", {"$arrayElemAt": ["$$value", 0]}]},
                  "then": 
                  {"$concatArrays": 
                    [["$$this.score"], [{"$arrayElemAt": ["$$value", 0]}]]}},
                {"case": 
                  {"$gte": ["$$this.score", {"$arrayElemAt": ["$$value", 1]}]},
                  "then": 
                  {"$concatArrays": 
                    [[{"$arrayElemAt": ["$$value", 0]}], ["$$this.score"]]}}],
              "default": "$$value"}}}}}}])