CODEWITHSUNDEEP
mongodb
Stuart Brown
Stuart Brown

Reputation: 987

MongoDB - Selecting Distinct Records Based on Multiple Fields

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.

enter image description here

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:

MongoShell in Compass Compass UI

Upvotes: 2

Views: 5464

Answers (1)

vishnu
vishnu

Reputation: 2011

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"
}

Upvotes: 4

Related Questions