CODEWITHSUNDEEP
mongodbaggregation-framework
SyncMaster
SyncMaster

Reputation: 9926

How to display additional fields in MongoDB's group aggregation query

I started learning MongoDB and I am trying to find how to display addtional fields in the group query.

Say I have the following document and I want to find the maximum score taken by a student in every class. 

{ "_id" : { "class_id" : 1, "student_id" : 40 }, "avgStudentScore" : 62 }
{ "_id" : { "class_id" : 1, "student_id" : 44 }, "avgStudentScore" : 79 }
{ "_id" : { "class_id" : 2, "student_id" : 0 }, "avgStudentScore" : 53 }
{ "_id" : { "class_id" : 2, "student_id" : 24 }, "avgStudentScore" : 60 }
{ "_id" : { "class_id" : 0, "student_id" : 15 }, "avgStudentScore" : 51 }
{ "_id" : { "class_id" : 0, "student_id" : 25 }, "avgStudentScore" : 66 }
{ "_id" : { "class_id" : 0, "student_id" : 30 }, "avgStudentScore" : 32 }

I can do that using the following group aggregation query.

{
    $group:
    {
        "_id" : 
        {
            "class_id" : "$_id.class_id",
        },
        "maxStudentScore" :
        {
            $max : "$avgStudentScore"
        }
    }
}

My result would be something like,

{ "_id" : { "class_id" : 1 }, "maxStudentScore" : 79 }
{ "_id" : { "class_id" : 2 }, "maxStudentScore" : 60 }
{ "_id" : { "class_id" : 0 }, "maxStudentScore" : 66 }

But I lost the student_id who got the maximum score. How can I display the student_id as well in my final result? Thanks.

Upvotes: 1

Views: 815

Answers (1)

s7vr
s7vr

Reputation: 75924

You can try sort before you group, so you can use the first to pick the student id.

aggregate([ {
    $sort: {
        "_id.class_id": 1,
        "avgStudentScore": -1
    }
},{
    $group: {
        "_id": {
            "class_id": "$_id.class_id",
        },
        "student_id": {
            $first: "$_id.student_id"
        },
        "maxStudentScore": {
            $max: "$avgStudentScore"
        }
    }
}])

Sample Output: 

{ "_id" : { "class_id" : 2 }, "student_id" : 24, "maxStudentScore" : 60 }
{ "_id" : { "class_id" : 1 }, "student_id" : 44, "maxStudentScore" : 79 }
{ "_id" : { "class_id" : 0 }, "student_id" : 25, "maxStudentScore" : 66 }

Upvotes: 3

Related Questions