group array of objects and get the count in mongodb

Question

I am having an array of objects:

[
  {
    medicationDetails: {
      category: 'Asthma',
      subCategory: '',
      providedBy: 'Doctor'
    }
  },
  {
    medicationDetails: {
      category: 'Diabetes',
      subCategory: 'Oral',
      providedBy: 'Nurse'
    }
  },
  {
    medicationDetails: {
      category: 'Asthma',
      subCategory: ''
      providedBy: 'Doctor'
    }
  },
  {
    medicationDetails: {
      category: 'Diabetes',
      subCategory: 'Insulin',
      providedBy: 'Doctor'
    }
  }
]

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

Need to group based on 'category', 'sub-category' and 'providedBy' and get the count like this:

[
  {
    medicationDetails: [
     {
      name: 'Asthma',
      providedByDoctorCount: 2,
      providedByNurseCount: 0
     },
     {
      name: 'Oral',
      providedByDoctorCount: 0,
      providedByNurseCount: 1
     },
     {
      name: 'Insulin',
      providedByDoctorCount: 1,
      providedByNurseCount: 0
     }
    ]
  }
]

I was tried with $group and $count but not getting the expected result. Need some valuable help.

cmgchess · Accepted Answer · 2023-06-04T16:23:09.867

somewhat similar to your previous question.
you can conditionally sum based on the value of the field when grouping

Create a key field based on category and subCategory values
Group by the key and add to providedByDoctor or providedByNure conditionally based on the value of providedBy
Group into a single medicationDetails array

db.collection.aggregate([
  { 
   $project: {
      providedBy: "$medicationDetails.providedBy",
      key: { $cond: [ { $eq: [ "$medicationDetails.subCategory", "" ] }, "$medicationDetails.category", "$medicationDetails.subCategory" ] }
    } 
  },
  {
    $group: { 
      _id: "$key",
      providedByDoctor: { $sum: { $cond: [ { $eq: [ "$providedBy", "Doctor" ] }, 1, 0 ] } },
      providedByNurse: { $sum: { $cond: [ { $eq: [ "$providedBy", "Nurse" ] }, 1, 0 ] } } }
  },
  { $group: { _id: null, medicationDetails: { $push: { name: "$_id", providedByDoctorCount: "$providedByDoctor", providedByNurseCount: "$providedByNurse" } } } },
  { $unset: "_id" }
])

playground

group array of objects and get the count in mongodb

1 Answers1