MongoDB Compass Select Distinct Field

Question

I'm trying to create an aggregation pipeline in MongoDB Compass for the field city in one of my collections. This is what I have so far:

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

How can I add the number of occurrences for each cities? I would like to have an array that consists of objects with city and count.

I managed to iron out a solution that gives me the data that I need. See my answer below. — Fredrik Tibbling, Jun 14 '20 at 09:03

score 0 · Answer 1 · answered Jun 08 '20 at 18:51

Query :

db.collection.aggregate([
    /** 
     * unwind city, if your desired response is just an array `[{city :..., count :... }]`
     * then you don't need to `preserveNullAndEmptyArrays`
     */
    {
      $unwind: "$city"
    },
    /** group on unique city's & count no.of occurrences */
    {
      $group: { _id: "$city", count: { $sum: 1 } }
    },
    /** group on all docs (without any condition) & push objects to an array */
    {
      $group: { _id: "", citiesAndCount: { $push: { city: "$_id", count: "$count" } } }
    }
  ])

Test : mongoplayground

Ref : aggregation-pipeline

score 0 · Answer 2 · answered Jun 14 '20 at 09:04

0

This works fine in MongoDB:

 const aggregatorOptionsCity = [
    { $match: req.query },
    {
      $group: {
        _id: '$city',
        count: { $sum: 1 },
      },
    },
  ];

answered Jun 14 '20 at 09:04

Fredrik Tibbling

23
3
8

This answer doesn't seem to match with your question !! this would produce an array of docs/objects `I would like to have an array that consists of objects with city and count` if this means aggregation result array then question is misleading !! Also not sure why you're `unwinding` in question & not unwinding in answer ! If not first stage `$unwind` & last stage `$group` both are the same.. – whoami - fakeFaceTrueSoul Jun 18 '20 at 20:26

MongoDB Compass Select Distinct Field

2 Answers2