MongoDB: How to Group By Multiple Fields

You can use the following syntax to group by multiple fields and perform some aggregation in MongoDB:

db.collection.aggregate([
    {$group : {_id:{field1:"$field1", field2:"$field2"}, count:{$sum:1}}}
])

The following examples show how to use this syntax with a collection teams with the following documents:

db.teams.insertOne({team: "Mavs", position: "Guard", points: 31})
db.teams.insertOne({team: "Mavs", position: "Guard", points: 22})
db.teams.insertOne({team: "Mavs", position: "Forward", points: 19})
db.teams.insertOne({team: "Rockets", position: "Guard", points: 26})
db.teams.insertOne({team: "Rockets", position: "Forward", points: 33})

Example 1: Group By Multiple Fields & Aggregate

We can use the following code to group by ‘team’ and ‘position’ and count the occurrences of each grouping:

db.teams.aggregate([
    {$group : {_id:{team:"$team", position:"$position"}, count:{$sum:1}}}
])

This returns the following results:

{ _id: { team: 'Rockets', position: 'Forward' }, count: 1 }
{ _id: { team: 'Mavs', position: 'Guard' }, count: 2 }
{ _id: { team: 'Mavs', position: 'Forward' }, count: 1 }
{ _id: { team: 'Rockets', position: 'Guard' }, count: 1 }

We could also perform a different aggregation. For example, we could group by ‘team’ and position’ and find the sum of ‘points’ by grouping:

db.teams.aggregate([
    {$group : {_id:{team:"$team", position:"$position"}, sumPoints:{$sum:"$points"}}}
])

This returns the following results:

{ _id: { team: 'Rockets', position: 'Forward' }, sumPoints: 33 }
{ _id: { team: 'Mavs', position: 'Guard' }, sumPoints: 53 }
{ _id: { team: 'Mavs', position: 'Forward' }, sumPoints: 19 }
{ _id: { team: 'Rockets', position: 'Guard' }, sumPoints: 26 }

This tells us:

• The sum of points scored by players on the ‘Rockets’ in position ‘Forward’ is 33.
• The sum of points scored by players on the ‘Mavs’ in position ‘Guard’ is 53.

And so on.

Example 2: Group By Multiple Fields & Aggregate (Then Sort)

We can use the following code to group by ‘team’ and position’ and find the sum of ‘points’ by grouping, then sort the results by ‘points’ in ascending order:

db.teams.aggregate([
    {$group : {_id:{team:"$team", position:"$position"}, sumPoints:{$sum:"$points"}}},
    {$sort : {sumPoints:1}}
])

This returns the following results:

{ _id: { team: 'Mavs', position: 'Forward' }, sumPoints: 19 }
{ _id: { team: 'Rockets', position: 'Guard' }, sumPoints: 26 }
{ _id: { team: 'Rockets', position: 'Forward' }, sumPoints: 33 }
{ _id: { team: 'Mavs', position: 'Guard' }, sumPoints: 53 }

We can use -1 to instead sort the results by points in descending order:

db.teams.aggregate([
    {$group : {_id:{team:"$team", position:"$position"}, sumPoints:{$sum:"$points"}}},
    {$sort : {sumPoints:-1}}
])

This returns the following results:

{ _id: { team: 'Mavs', position: 'Guard' }, sumPoints: 53 }
{ _id: { team: 'Rockets', position: 'Forward' }, sumPoints: 33 }
{ _id: { team: 'Rockets', position: 'Guard' }, sumPoints: 26 }
{ _id: { team: 'Mavs', position: 'Forward' }, sumPoints: 19 }

Note: You can find the complete documentation for $group here.