group by day/month and take average of rating on that day/month in mongo
To group documents by day/month and return the month key in your output, you need to first $project
the key fields to appropriate formats using the Date operators, in particular the $dateToString
and $month
operators.
This can be done in a $project
stage prior to the $group
step but not necessary since the $group
pipeline accommodates mostly the accumulator operators.
In the preceding $group
pipeline, you can the group the documents by the formatted date key, aggregate using the $avg
operator and return the month as an integer from the previous pipeline using $first
accumulator operator.
Running the following aggregation pipeline should give you the desired result:
db.collection.aggregate([ { "$group": { "_id": { "$dateToString": { "format": "%Y-%m-%d", "date": "$ceatedAt" } }, "average": { "$avg": "$rating" }, "month": { "$first": { "$month": "$ceatedAt" } }, } }])
For the collection, use an aggregate()
to $group
, with the grouping _id
of the $month
from the ceatedAt
(sic) field of your documents, and make the month
field in the outputted document equal that value.
Then, create a field named ceatedAt
that uses $dateToString
to format
the $date
field of the inputted document as "%Y-%m-%d"
.
Finally, create a field named average
, that is the $avg
of the $rating
field of the inputted document, in your grouped documents.
db.collection.aggregate([ { $group: { _id: { month: { $month: "$ceatedAt" } }, date: { first: "$ceatedAt" }, average: { $avg: "$rating" } } }, { $project: { month: "$month", ceatedAt: { $dateToString: { format: "%Y-%m-%d", date: "$date" } }, average: "$average" } }])