group multiple fields and get respective counts using mongodb

Question

I am having some data:

{
  medicationDetails: {
    category: 'Asthma',
    subCategory: ''
  }
},
{
  medicationDetails: {
    category: 'Diabetes',
    subCategory: 'Oral'
  }
},
{
  medicationDetails: {
    category: 'Asthma',
    subCategory: ''
  }
},
{
  medicationDetails: {
    category: 'Diabetes',
    subCategory: 'Insulin'
  }
}

Some medication will have sub-category and some doesn't.

Need to group and get the count of medications like this:

{
  medicationDetails: [
    {
        name: 'Asthma',
        count: 2
    },
    {
        name: 'Oral',
        count: 1
    },
    {
        name: 'Insulin',
        count: 1
    }
  ]
}

Tried aggregation with $group and $count, but not getting what exactly needed.

Need some valuable help.

Answer 1

One option is to use $cond and group twice:

db.collection.aggregate([
  {$project: {
      key: {$cond: [
          {$eq: ["$medicationDetails.subCategory", ""]},
          "$medicationDetails.category",
          "$medicationDetails.subCategory"
      ]}
  }},
  {$group: {_id: "$key", count: {$sum: 1}}},
  {$group: {_id: 0, medicationDetails: {$push: {name: "$_id", count: "$count"}}}},
  {$unset: "_id"}
])

See how it works on the playground example