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
}
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"}}
}}
])