I have student grading system which makes use of MongoDB. I have the following documents in MongoDB:
How do I get the number of students who got grade "A","B","C","D" for a particular subject. The "_id" for every subject remains the same.
{
_id: "60b223541338467beaf3ae0d",
studentName: "John Doe"
studentGradingDetails: [
{
_id: "60b21e47e5462929cab27a98",
term: "semester-1",
subject: "chemistry",
grade: "A",
createdAt: "2021-05-29T10:58:15.113Z",
},
{
_id: "60b21e47e5462929cab27a99",
term: "semester-2",
subject: "computer_science",
grade: "B",
createdAt: "2021-05-29T10:58:15.113Z",
},
],
createdAt: "2021-05-29T11:19:48.770Z",
}
{
_id: "60b223541338467beaf3ae0e",
studentName: "Will Smith"
studentGradingDetails: [
{
_id: "60b21e47e5462929cab27a98",
term: "semester-1",
subject: "chemistry",
grade: "D",
createdAt: "2021-05-29T10:58:15.113Z",
},
{
_id: "60b21e47e5462929cab27a99",
term: "semester-2",
subject: "computer_science",
grade: "A",
createdAt: "2021-05-29T10:58:15.113Z",
},
],
createdAt: "2021-05-29T11:19:48.770Z",
}
This is what I have tried and stuck, not sure what the next steps are !?
await db.collection("studentsemestergrades")
.aggregate([
{ $unwind: "$studentGradingDetails" },
{
$group: {
_id: "$studentGradingDetails._id",
},
},
])
.toArray();
Expected Output:
Distribution of students scoring different grades for each subject (_id)
{
"_id" : "60b21e47e5462929cab27a98",
"A" : 12,
"B" : 20,
"C" : 8,
"D" : 2
},
{
"_id" : "60b21e47e5462929cab27a99",
"A" : 5,
"B" : 2,
"C" : 8,
"D" : 12
}
$unwind deconstruct the studentGradingDetails array$group by _id and grade and get total count$group by only _id and construct the key-value pair array of object with grade and count$arrayToObject convert above key-value pair array to object$mergeObjects to merge above converted object and _id field you can add more fields if needed$replaceRoot to replace above merge object to rootawait db.collection("studentsemestergrades").aggregate([
{ $unwind: "$studentGradingDetails" },
{
$group: {
_id: {
_id: "$studentGradingDetails._id",
grade: "$studentGradingDetails.grade"
},
count: { $sum: 1 }
}
},
{
$group: {
_id: "$_id._id",
grades: {
$push: {
k: "$_id.grade",
v: "$count"
}
}
}
},
{
$replaceRoot: {
newRoot: {
$mergeObjects: [
{ _id: "$_id" },
{ $arrayToObject: "$grades" }
]
}
}
}
])