Mongo Pipeline with multiple group operation and project operation

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