MongoDB Compass: select distinct field values

Question

I am using MongoDB Compass and don't have Mongo Shell. I need to build a query using MongoDB Compass tool to select distinct values of the "genre" field from my collection.

Sample Input:

{"_id":{"$oid":"58c59c6a99d4ee0af9e0c34e"},"title":"Bateau-mouche sur la Seine","year":{"$numberInt":"1896"},"imdbId":"tt0000042","genre":["Documentary”,”Short”],"viewerRating":{"$numberDouble":"3.8"},"viewerVotes":{"$numberInt":"17"},"director":"Georges Mlis"}
{"_id":{"$oid":"58c59c6a99d4ee0af9e0c340"},"title":"Watering the Flowers","year":{"$numberInt":"1896"},"imdbId":"tt0000035","genre":["Short”],"viewerRating":{"$numberDouble":"5.3"},"viewerVotes":{"$numberInt":"33"},"director":"Georges M�li�s"}
{"_id":{"$oid":"58c59c6a99d4ee0af9e0c34a"},"title":"The Boxing Kangaroo","year":{"$numberInt":"1896"},"imdbId":"tt0000048","genre":["Short”],"viewerRating":{"$numberDouble":"5.2"},"viewerVotes":{"$numberInt":"48"},"director":"Birt Acres"}

Expected output: Documentary, Short

Guide

"... don't have mongo Shell", A MongoDB installation includes Mongo Shell program. Also, please post text of a sample document (you can copy from Compass). – Payoff

Thanks Prasad for your response. I installed only MongoDB Compass and connecting to MongoDB Atlas cluster. I know how to create the query in mongo shell to select distinct fields. just curious to learn, how to achieve the same with compass tool as I will be using it frequently. will attach few test docs. – Guide

You can use Compass's Aggregation Pipeline Builder to create aggregattion queries using grouping to get count, sum, average, etc. Also, see Aggregation Pipeline Stages and Aggregation operators. – Payoff

Answer 1

You can do this via aggregation framework in Compass, using $unwind and $group. The $unwind is performed to create a unique document for each element in the target array, which enables the $addToSet operator in the $group stage to then capture the genres as distinct elements.

Pipeline:

[
  {
    $unwind: {
      path: '$genre',
      preserveNullAndEmptyArrays: true
    }
  },
  {
    $group: {
      _id: null,
      uniqueGenres: { $addToSet: '$genre' }
    }
  }
]

See screenshot below for Compass example:

Camaraderie

thanks for the answer. just a quick question how to convert "Short,Documentary" to array, pl suggest – Guide

In the output above, "Short", "Documentary" are an array within the field distinctGenres in the single document that is returned. If you want to the final output to just be an array, then you can do that in your server-side code. i.e. javascript const pipeline = [ { $unwind: { path: '$genre', preserveNullAndEmptyArrays: true } }, { $group: { _id: null, uniqueGenres: { $addToSet: '$genre' } } } ]; const [{ distinctGenres }] = await db.getCollection('docs').aggregate(pipeline).toArray(); – Camaraderie

Thanks for your suggestion. I have created my pipeline based on your inputs, kindly review and suggest if I missed anything. – Guide

This should be the accepted answer, not your own slight modification. – Hymie

I didn't need the path but the group provided exactly what I needed for my use case. Thank you for your post. – Potamic

how can i download the filtered records – Escobedo