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
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:
Query
[-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$setWindowFields could be used*here you dont need unwind/group/sort etc because only 2.
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"}}}}}}])