CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
kaoscify
kaoscify

Reputation: 1763

How to find most common value for specific categories in MongoDB?

I have a dataset in MongoDB that looks like this:

{ "name": "Tom's", "category": "coffee shop" },
{ "name": "Red Lobster", "category": "restaurant" },
{ "name": "Tom's", "category": "coffee shop" },
{ "name": "Starbucks", "category": "coffee shop" },
{ "name": "Central Park", "category": "park" },
{ "name": "Office", "category": "office" },
{ "name": "Red Lobster", "category": "restaurant" },
{ "name": "Home", "category": "home" },
{ ... } // and so on

How can I find the most common value for specific categories? For example, the most common occurring value for coffee shop and restaurant should be Tom's and Red Lobster, respectively.

My current $aggregate query only seems to list the most common occurring value among ALL of the dataset:

db.collection.aggregate(
{ "$group": { "_id": { "name": "$name" }, "count": { "$sum":1 } }}, 
{ "$group": { "_id": "$_id.name", "count": { "$sum": "$count" } }}, 
{ "$sort": { "count":-1 }}
)

Upvotes: 2

Views: 7089

Answers (1)

s7vr
s7vr

Reputation: 75984

You can try the below query.

$group on category and name to get the count for each category and name combination.

$sort the input documents by category and count desc.

$group on category with $first to pick the document with most occurrences.

db.collection_name.aggregate([
  {
    "$group": {
      "_id": {
        "category": "$category",
        "name": "$name"
      },
      "count": {
        "$sum": 1
      }
    }
  },
  {
    "$sort": {
      "_id.category": 1,
      "count": -1
    }
  },
  {
    "$group": {
      "_id": {
        "category": "$_id.category"
      },
      "name": {
        "$first": "$_id.name"
      },
      "count": {
        "$first": "$count"
      }
    }
  }
])

Upvotes: 5

Related Questions