Search code examples
mongodbnosqlmongodb-queryaggregation-frameworknosql-aggregation

Grouping and summing an embedded array by month


I have the following schema :

_id 
dates : 
    date :
       year
       month
       day
other unrelated fields

I already grouped each _id such that dates contain multiple date objects (which contain the year, month, day). I want to now group each date object by year and month such that I get the count of the number of dates corresponding to the year and month. For example, if I have the following document :

_id : 124567789554
dates : 
    date : 
        year : 2018
        month : 9
        day : 1
    date : 
        year : 2018
        month : 9
        day : 2
    date : 
        year : 2018
        month : 9
        day : 3
    date : 
        year : 2018
        month : 10
        day : 1

The output that I want is :

_id : 124567789554
dates : 
    date : 
        year : 2018
        month : 9
        count : 3
    date : 
        year : 2018
        month : 10
        count : 1

How can I go about doing this?

Edit : For some additional context, I first have to group by a personId. Initially the schema looks like this :

_Id (automatically generated by mongoDB)
personId 
date

There are multiple rows with the same personId, corresponding to a date. I have to first group such that _Id = personId, and aggregate the dates together. How can I do both at the same time? My current query:

{
  _id: "$personId",
  dates: {
    $addToSet: "$date"
  },
  other unrelated fields
}

Solution

  • Use $group to group by person id, month and year and count the no of matches followed by $group to collect all the dates with year and month and count for each person id.

    db.colname.aggregate([
      {"$group":{
        "_id":{"personId":"$personId","year":"$date.year","month":"$date.month"},
        "count":{"$sum":1}
      }},
      {"$group":{
        "_id":"$_id.personId",
        "dates":{"$push":{"year":"$_id.year","month":"$_id.month","count":"$count"}}
      }}
    ])