Mongo pipeline to sum all marks studentWise

Write a mongo query for Mongo pipeline to sum all marks studentWise. 

Before executing this pipeline view previous pipeline.

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 the 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"}}}},
    {$unwind:"$data"},
    {$group:{"_id":"$_id",totalYearMarks:{$sum:"$data.totalMarks"}}}
    ]);
    

Output: 

[
        "_id" : "Amit Sainik"
        "totalYearMarks" : 559.0
    }
    { 
        "_id" : "Rahul Meing"
        "totalYearMarks" : 596.0
    }
    
]


No comments:

Post a Comment