MongoDB: How to Select Distinct Values from Multiple Fields


You can use the following syntax to select distinct values from multiple fields in MongoDB:

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

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: "Rockets", position: "Center", points: 19})
db.teams.insertOne({team: "Rockets", position: "Forward", points: 26})
db.teams.insertOne({team: "Rockets", position: "Forward", points: 29}) 
db.teams.insertOne({team: "Cavs", position: "Guard", points: 33})

Example: Select Distinct Values from Multiple Fields

We can use the following query to find all of the distinct values from the team and position fields:

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

This query returns the following documents:

{ _id: { team: 'Mavs', position: 'Guard' } }
{ _id: { team: 'Rockets', position: 'Forward' } }
{ _id: { team: 'Rockets', position: 'Center' } }
{ _id: { team: 'Cavs', position: 'Guard' } }

And we can use the following query to find all of the distinct values from the team, position, and points fields:

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

This query returns the following documents:

{ _id: { team: 'Cavs', position: 'Guard', points: 33 } }
{ _id: { team: 'Rockets', position: 'Forward', points: 29 } }
{ _id: { team: 'Mavs', position: 'Guard', points: 22 } }
{ _id: { team: 'Rockets', position: 'Forward', points: 26 } }
{ _id: { team: 'Mavs', position: 'Guard', points: 31 } }
{ _id: { team: 'Rockets', position: 'Center', points: 19 } }

Notice that all of the documents are returned since there are no two documents that have identical values for the team, position, and points fields.

Additional Resources

The following tutorials explain how to perform other common operations in MongoDB:

MongoDB: How to Add a New Field in a Collection
MongoDB: How to Group By and Count
MongoDB: How to Group By Multiple Fields

Leave a Reply

Your email address will not be published.