CODEWITHSUNDEEP
mongodbaggregation-frameworkdatabase
Arganzas
Arganzas

Reputation: 3

MongoDB docs $group

I have a document in MongoDB:

student{
    id: ...id
    name: ...
    student:[{ 
        group_number: ...
        results:[{subj: ... , mark: 5},{...},{...}]
    }]

}

And I need to use 

db.student.aggregate({$group: {_id : "$_id", 
                               mark_total : {$sum : $student.results.mark}}

})

the problem is that $student.results.mark returns undefined so aggregate returns 

"result":[
    {
    "_id" : 2,
    "mark_total: : 0
    }
 ],
"ok" : 1

as $sum return nothing

What do you advise?

Upvotes: 0

Views: 35

Answers (1)

somallg
somallg

Reputation: 2043

You student.results is a array, access its with $student.results.mark returns nothing, that's used for access field of embedded document. You will need to make each element of the element to become embedded element first with $unwind stage

Sample data

db.student.insert([{
    name: 'Foo',
    student:[{ 
        group_number: 1,
        results:[{subj: 'Math', mark: 5}, {subj: 'Info', mark: 4}, {subj: 'English', mark: 3}]
    }]
 }])

Final Query

db.student.aggregate([
    { "$unwind": "$student" },
    { "$unwind": "$student.results" },
    { "$group": { _id: "$_id", mark_total: { $sum: "$student.results.mark" } } }
]);

Final Output

{ "_id" : ObjectId("566790f6e1f3a57c81bdf2f9"), "mark_total" : 12 }

Because student field is an array, the first stage { "$unwind": "$student" } will make { group_number: 1, results:[{subj: 'Math', mark: 5}, {subj: 'Info', mark: 4}, {subj: 'English', mark: 3}]} to become embedded document. Output after first $unwind stage

{ "_id" : ObjectId("566790f6e1f3a57c81bdf2f9"), "name" : "Foo", "student" : { "group_number" : 1, "results" : [ { "subj" : "Math", "mark" : 5 }, { "subj" : "Info", "mark" : 4 }, { "subj" : "English", "mark" : 3 } ] } }

Then you need to unwind again because "$student.results" is still an array Output after 2nd unwind

{ "_id" : ObjectId("566790f6e1f3a57c81bdf2f9"), "name" : "Foo", "student" : { "group_number" : 1, "results" : { "subj" : "Math", "mark" : 5 } } }
{ "_id" : ObjectId("566790f6e1f3a57c81bdf2f9"), "name" : "Foo", "student" : { "group_number" : 1, "results" : { "subj" : "Info", "mark" : 4 } } }
{ "_id" : ObjectId("566790f6e1f3a57c81bdf2f9"), "name" : "Foo", "student" : { "group_number" : 1, "results" : { "subj" : "English", "mark" : 3 } } }

You can now access the mark with "$student.results.mark"

Upvotes: 1

Related Questions