MongoDB - Selecting Distinct Records Based on Multiple Fields

Question

I have a collection in MongoDB recording information about sport cards. Each document has values for the sport, manufacturer, brand and variation (see screengrab below). There are a number of cards associated with each variation.

I would like to write a query that provides one record for each combination of sport, manufacturer, brand and variation (ignoring that there are multiple cards for each variation). So I would end up with something like

Football, Upper Deck, Exquisite, Signature patch
Football, Upper Deck, Exquisite, Foursome Signature patch
Football, Upper Deck, some-other-brand, some-other-variation
Football, Topps, some-other-brand, some-other-variation
Baseball, Topps, some-other-brand, some-other-variation

I've tried a query such as:

db.checklists.aggregate([
    {
       $group:
       {
          _id:0,
          manufacturer: {$addToSet: '$manufacturer'},
          brand: {$addToSet: '$brand'},
          variation: {$addToSet: '$variation'},
    }}
 ]);

but it returns an empty array. Can anyone help?

Adding some images or errors I see when running the aggregation suggested in one of the answers below:

user_9148170 · Answer

You can use the aggregate function like below in order to group based on multiple fields

db.collection.aggregate([
  {
    $group: {
      _id: {
        manufacturer: "$manufacturer",
        brand: "$brand",
        variation: "$variation"
      }
    }
  },
  {
    "$project": {
      _id: 0,
      manufacturer: "$_id.manufacturer",
      brand: "$_id.brand",
      variation: "$_id.variation"
    }
  }
])

Edit

Regarding Mongo Shell (via Compass) returning empty array

Based on the screenshots attached in the question, it looks like the collection name is wrong. Replace the db.collection.aggregate with db.checklists.aggregate. Also by default, the shell in compass connects to the test db by default. Please make sure you have selected the correct db before executing the query.

Regarding Aggregation builder error in Compass

In the $group stage you just need to only add the below section

{
  "_id": {
    "manufacturer": "$manufacturer",
    "brand": "$brand",
    "variation": "$variation"
  }
}

And In the $project stage

{
  "_id": 0,
  "manufacturer": "$_id.manufacturer",
  "brand": "$_id.brand",
  "variation": "$_id.variation"
}

MongoDB - Selecting Distinct Records Based on Multiple Fields

1 Answers