Table of Contents
MongoDB allows us to group documents by multiple fields, which can be useful when we want to aggregate data from multiple fields. This is done using the $group operator, which takes a list of fields as an argument and returns a new document for each group created by the fields. The $group operator also allows us to apply an aggregation operator to each field, such as sum, average, min, max, and count. This allows us to quickly and easily analyze data across 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)
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 .