Write a query for Mongo Pipeline with multiple group operations and project operations.
Example:
[{"_id" : ObjectId("602363701b62b07195af70d7"),"studentName" : "Rahul Meing","rollNo" : 1234.0,"subjectWiseMarks" : {"term1" : {"english" : 35.0,"hindi" : 59.0,"maths" : 76.0,"science" : 65.0,"computer" : 63.0},"term2" : {"english" : 35.0,"hindi" : 59.0,"maths" : 76.0,"science" : 65.0,"computer" : 63.0}},"class" : 9.0}{"_id" : ObjectId("602363b61b62b07195af70d8"),"studentName" : "Amit Sainik","rollNo" : 1235.0,"subjectWiseMarks" : {"term1" : {"english" : 39.0,"hindi" : 59.0,"maths" : 70.0,"science" : 60.0,"computer" : 61.0},"term2" : {"english" : 33.0,"hindi" : 51.0,"maths" : 78.0,"science" : 45.0,"computer" : 63.0}},"class" : 9.0}]
Approach
Query: Query to find sum of marks of each student termwise.
db.getCollection("Student").aggregate([{$match:{"class":9}},{$project:{"studentName":1,"rollNo":1,"marks":{$objectToArray:"$subjectWiseMarks"}}},{$unwind:"$marks"},{$group:{"_id":{"studentName":"$studentName","term":"$marks.k","marks":"$marks.v"}}},{$project:{"_id.studentName":1,"_id.term":1,"marks":{$objectToArray:"$_id.marks"}}},{$unwind:"$marks"},{$group:{"_id":{"studentName":"$_id.studentName","term":"$_id.term"},"totalMarks":{$sum:"$marks.v"}}},{$group:{"_id":"$_id.studentName","data":{$push:{"term":"$_id.term","totalMarks":"$totalMarks"}}}}]);
Output:
[{"_id" : "Amit Sainik","data" : [{"term" : "term2","totalMarks" : 270.0},{"term" : "term1","totalMarks" : 289.0}]}{"_id" : "Rahul Meing","data" : [{"term" : "term1","totalMarks" : 298.0},{"term" : "term2","totalMarks" : 298.0}]}]
No comments:
Post a Comment