Getting the name of the field with maximum count in mongodb

Question

I am new to mongodb and want to get the name of the field(spare part type) which has the maximum count! A sample document in my collection(original collection has 50 documents) is given below

[
  {
    "Vehicle": {
      "licensePlateNo": "111A",
      "vehicletype": "Car",
      "model": "Nissan Sunny",
      "VehicleCategory": [
        {
          "name": "Passenger"
        }
      ],
      "SparePart": [
        {
          "sparePartID": 4,
          "Type": "Wheel",
          "Price": 10000,
          "Supplier": [
            {
              "supplierNo": 10,
              "name": "Saman",
              "contactNo": 112412634
            }
          ]
        }
      ],
      "Employee": [
        {
          "employeeNo": 3,
          "job": "Painter",
          "jobCategory": "",
          "salary": 100000
        }
      ]
    }
  }
]

How can i write a query to obtain the name of the spare part with the highest count?

there is no count i want to execute a query like select sparepart type from vehicle where max(count) — Suneth Perera
db.AutoSmart.count({"Vehicle__SparePart__Type":"Wheel"}) when i run this i get the number of occurences of wheel! i want to retrieve the spare part type with maximum number of occurences — Suneth Perera

chridam chridam · Accepted Answer · 2016-12-05T13:16:06

Use the aggregation framework for this type of query. In particular you'd need to run an aggregation operation where the pipeline consists of the following stages (in order):

$unwind - You need this as the first pipeline step in order to flatten the SparePart array so that you can process the documents as denormalised further down the pipeline. Without this you won't get the desired result since the data will be in array format and the accumulator operators within the preceding stage work on single documents to aggregate the counts.

$group - This step will calculate the counts for you, for documents grouped by the Type field. The accumulator operator $sum will return the total number of documents with each group.

$sort - As you get the results from the previous $group pipeline, you would need to order the documents by the count field so that you get the top document with the most counts.

$limit - This will give you the top document.

Now, assembling the above together you should run the following pipeline to get the desired result:

db.AutoSmart.aggregate([
    { "$unwind": "$Vehicle.SparePart" },
    {
        "$group": {
            "_id": "$Vehicle.SparePart.Type",
            "count": { "$sum": 1 }
        }
    },
    { "$sort": { "count": -1 } },
    { "$limit": 1 } 
])

Getting the name of the field with maximum count in mongodb

2 Answers