2

I would like to be able to get all the distinct values for a field or nested field in MongoDB Compass.

blakesters
  • 81
  • 2
  • 10
  • https://docs.mongodb.com/manual/reference/command/distinct/ – Robert Harvey Nov 21 '19 at 19:18
  • What about the MongoDB Compass GUI? – blakesters Nov 21 '19 at 19:20
  • Does it allow you to execute arbitrary queries? The documentation for the Compass UI is pretty scant. [This person](https://medium.com/@nirajmind/a-long-wait-comes-to-an-end-with-compass-6f8e92260588) says that Compass is capable of doing what you describe, but doesn't explain how. Presumably, they figured it out by playing with the UI. – Robert Harvey Nov 21 '19 at 19:23
  • Not really. From what I know you can just do find() and aggregations. I found this SO question about a way to find distinct values via aggregation, which will work for me. https://stackoverflow.com/questions/16368638/mongodb-distinct-aggregation Mongo Compass shows unique values out of the box if you click on the Schema tab, but only if they are relatively substantial in their percentage of the total values, so it doesn't look exhaustive. The article you posted reminded me you can see distinct values out of the box. Thanks for your help! – blakesters Nov 21 '19 at 19:38
  • After thinking it through some more, this task is better accomplished with the CLI. Thanks for pointing me to distinct in the Mongo docs. – blakesters Nov 21 '19 at 19:51

1 Answers1

3

Here's a way that I've been using in Compass.

If you go into Compass to the collection you want and go to the aggregation tab, you can unwind and group as such:

Select $unwind and then fill in the field you want, prefixed with $. I usually ignore null and empty arrays if the field is an array.

{
  path: '$displayFirstName',
  preserveNullAndEmptyArrays: false
}

Then use a $group stage to group the values and add them to a set:

{
  _id: null,
  uniqueFirstNames: {
    $addToSet: '$displayFirstName'
  }
}

The unique list of values will be displayed on the right of the $group stage.

If the value is nested and you want to flatten the results, just add more $unwind stages per level before the final $group stage. For example, if you had user.addresses which is an array of objects and you wanted to get all the possible address types of all your users as a flattened list you could do this.

$unwind

{
  path: '$addresses',
  preserveNullAndEmptyArrays: false
}

2nd $unwind

{
  path: '$addresses.type',
  preserveNullAndEmptyArrays: false
}

$group

{
  _id: null,
  uniqueAddressTypes: {
    $addToSet: '$addresses.type'
  }
}
blakesters
  • 81
  • 2
  • 10