Get count of each unique column field by date from mongodb data

user_11136720 0
Viewed 32

I would like to get count of each unique column field by date from mongodb data. Please see the example below.

Here I need result to display Monthly data of each subject with total count of each subject. I have tried grouping but couldn't get the actual output needed.

Student collection:

[{
   _id: uniqueId,   
   Date: '201507', // Year and month
   Subject: 'Maths',
},
{
   _id: uniqueId,   
   Date: '201507', // Year and month
   Subject: 'Physics'
},
{
   _id: uniqueId,   
   Date: '201508', // Year and month
   Subject: 'Maths'
},
{
   _id: uniqueId,   
   Date: '201507', // Year and month
   Subject: 'Maths'
}
]

The output will be

[
  {
    Date: 201507,
    Subject: {
                Maths: 2, // count
                Physics: 1
             }
  },
  {
    Date: 201508,
    Subject: {
                Maths: 1, // count                
             }
  }
]
mongoose mongodb
1 Answers

You can use this aggregate query:

  • First $group by Subject and Date as _id and creating a field called total where get the total times a subject is for a date.
  • Then $project to create the object with keys k and v (used later to transform an array to an object).
  • $group again by date to get all subjects for each date.
  • $project using $arrayToObject to transform the array into an object.
db.collection.aggregate([
  {
    "$group": {
      "_id": {
        "Subject": "$Subject",
        "Date": "$Date"
      },
      "total": {
        "$sum": 1
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "Date": "$_id.Date",
      "Subject": {
        "k": "$_id.Subject",
        "v": "$total"
      }
    }
  },
  {
    "$group": {
      "_id": "$Date",
      "Subject": {
        "$push": "$Subject"
      }
    }
  },
  {
    "$project": {
      "_id": 0,
      "Subject": {
        "$arrayToObject": "$Subject"
      },
      "Date": "$_id"
    }
  }
])

Example here

user_134642790
Related