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:
$match
student_id
condition$unwind
deconstructscores
array$match
type: exam
condition$sort
documents byscore
in descending order$group
bystudent_id
and get first root document$replaceRoot
to replacedoc
to 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" } }
]