46

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.

compass tool screenshot

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

halfer
  • 19,824
  • 17
  • 99
  • 186
javapedia.net
  • 2,531
  • 4
  • 25
  • 50
  • _"... 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). – prasad_ Nov 01 '19 at 02:54
  • 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. – javapedia.net Nov 01 '19 at 03:00
  • You can use [Compass's Aggregation Pipeline Builder](https://docs.mongodb.com/compass/master/aggregation-pipeline-builder/) to create aggregattion queries using grouping to get count, sum, average, etc. Also, see [Aggregation Pipeline Stages](https://docs.mongodb.com/manual/reference/operator/aggregation-pipeline/) and [Aggregation operators](https://docs.mongodb.com/manual/reference/operator/aggregation/). – prasad_ Nov 01 '19 at 03:53

1 Answers1

86

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:

enter image description here

christian
  • 1,585
  • 1
  • 11
  • 13
  • thanks for the answer. just a quick question how to convert "Short,Documentary" to array, pl suggest – javapedia.net Nov 04 '19 at 10:22
  • 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(); ``` – christian Nov 04 '19 at 10:48
  • Thanks for your suggestion. I have created my pipeline based on your inputs, kindly review and suggest if I missed anything. – javapedia.net Nov 06 '19 at 03:32
  • 6
    This should be the accepted answer, not your own slight modification. – Gerben Limburg May 07 '20 at 12:16
  • I didn't need the path but the group provided exactly what I needed for my use case. Thank you for your post. – Jeff C. Nov 29 '21 at 21:05
  • how can i download the filtered records – Sunil Garg Dec 11 '21 at 08:43