This is an example of the collection I am working with
> db.grades.find().limit(5).forEach(printjson)
{
"_id" : ObjectId("50b59cd75bed76f46522c353"),
"student_id" : 0,
"class_id" : 30,
"scores" : [
{
"type" : "exam",
"score" : 14.34345947841966
},
{
"type" : "quiz",
"score" : 47.65945482174327
},
{
"type" : "homework",
"score" : 83.42772189120254
},
{
"type" : "homework",
"score" : 49.86812935368258
},
{
"type" : "homework",
"score" : 39.85525554437086
}
]
}
{
"_id" : ObjectId("50b59cd75bed76f46522c356"),
"student_id" : 0,
"class_id" : 27,
"scores" : [
{
"type" : "exam",
"score" : 60.19473636151568
},
{
"type" : "quiz",
"score" : 64.15966210014162
},
{
"type" : "homework",
"score" : 82.80835343023551
}
]
}
{
"_id" : ObjectId("50b59cd75bed76f46522c350"),
"student_id" : 0,
"class_id" : 5,
"scores" : [
{
"type" : "exam",
"score" : 88.22950674232497
},
{
"type" : "quiz",
"score" : 79.28962650427184
},
{
"type" : "homework",
"score" : 18.66254946562674
},
{
"type" : "homework",
"score" : 40.28154176513361
},
{
"type" : "homework",
"score" : 1.23735944117882
},
{
"type" : "homework",
"score" : 88.96101200683958
}
]
}
{
"_id" : ObjectId("50b59cd75bed76f46522c357"),
"student_id" : 0,
"class_id" : 11,
"scores" : [
{
"type" : "exam",
"score" : 58.83297411100884
},
{
"type" : "quiz",
"score" : 49.66835710930263
},
{
"type" : "homework",
"score" : 18.05861540807023
},
{
"type" : "homework",
"score" : 80.04086698967356
}
]
}
{
"_id" : ObjectId("50b59cd75bed76f46522c358"),
"student_id" : 0,
"class_id" : 10,
"scores" : [
{
"type" : "exam",
"score" : 30.93065784731665
},
{
"type" : "quiz",
"score" : 55.98003281528393
},
{
"type" : "homework",
"score" : 55.6752702814148
},
{
"type" : "homework",
"score" : 63.15391302252755
}
]
}
What I'm trying to achieve, is to get the highest score of the exam, where the student id is 5. I've been stuck on this for quite a while, and the furthest I've managed to come is to retrieve all of the student's exam scores, however I haven't managed to make it so only the highest scoring one displays. This is another aspect I'm stuck on.
This is the code for this output:
{
"student_id" : 5,
"class_id" : 18,
"scores" : [
{
"type" : "exam",
"score" : 73.04238861317688
}
]
}
{
"student_id" : 5,
"class_id" : 8,
"scores" : [
{
"type" : "exam",
"score" : 22.38732080941065
}
]
}
{
"student_id" : 5,
"class_id" : 0,
"scores" : [
{
"type" : "exam",
"score" : 43.64758440439862
}
]
}
{
"student_id" : 5,
"class_id" : 16,
"scores" : [
{
"type" : "exam",
"score" : 33.39752665396672
}
]
}
{
"student_id" : 5,
"class_id" : 30,
"scores" : [
{
"type" : "exam",
"score" : 73.48459944869943
}
]
}
{
"student_id" : 5,
"class_id" : 19,
"scores" : [
{
"type" : "exam",
"score" : 15.36563152024366
}
]
}
{
"student_id" : 5,
"class_id" : 23,
"scores" : [
{
"type" : "exam",
"score" : 21.58296008740177
}
]
}
The code that gets me this is as follows:
var pipeline = [
{ $match: {student_id: 5} },
{ $unwind: "$scores" },
{ $group: {
_id: "$_id",
"student_id": { "$first": "$student_id" },
"class_id": { "$first": "$class_id" },
scores: { $push: "$scores" } } },
{ $project: { _id: 0,
student_id: 1,
class_id: 1,
scores: { $slice: [ "$scores", 1] } } } ];
var results = db.grades.aggregate ( pipeline );
results.forEach(printjson)
(Sorry for the poor structuring, I did my best but I'm not used too it on stackoverflow) I've been coding with MongoDB for about 2 days now, and I'm knowledgeable in it. Even less so with aggregates, but looking at posts and other code this seemed like the way to do it. From my point of view, because I'm already slicing through it, and attempting to sort the score will only result in getting the highest score out of exams, quiz and homeworks, so it's not a guarantee to give me the exam. Unless there's a different way to sort through these individually
Ideally, I'd want the end result to return only one document, where the exam score is the highest:
{
"student_id" : 5,
"class_id" : 30,
"scores" : [
{
"type" : "exam",
"score" : 73.48459944869943
}
]
}
CodePudding user response：
$matchstudent_idcondition$unwinddeconstructscoresarray$matchtype: examcondition$sortdocuments byscorein descending order$groupbystudent_idand get first root document$replaceRootto replacedocto root
var pipeline = [
{ $match: { student_id: 5 } },
{ $unwind: "$scores" },
{ $match: { "scores.type": "exam" } },
{ $sort: { "scores.score": -1 } },
{
$group: {
_id: "$student_id",
doc: { $first: "$$ROOT" }
}
},
{ $replaceRoot: { newRoot: "$doc" } }
]