Groups by day / month and average rate this day / month in mongo

Question

I have this data in mongodb:

[ { "rating": 4, "ceatedAt": ISODate("2016-08-08T15:32:41.262+0000") }, { "rating": 3, "createdAt": ISODate("2016-08-08T15:32:41.262+0000") }, { "rating": 3, "ceatedAt": ISODate("2016-07-01T15:32:41.262+0000") }, { "rating": 5, "createdAt": ISODate("2016-07-01T15:32:41.262+0000") } ] 

I need to get the average rating for this particular day / month depending on the filter criteria. if the filter range for the date is less than a month, then it will be grouped per day. if the filter range for a date is more than a month, then it will be grouped per month.

Here is what i expect

 [ { "average": 3.5, "ceatedAt": "2016-08-08", "month": "August" }, { "average": 4, "ceatedAt": 2016-07-01, "month": "July" } ] 

How can i do this in mongodb?

Answer 1

To group documents by day and month and return the key of the month to your output, you first need $project key fields in the appropriate formats using Date operators, in particular $dateToString and $month . This is done at the $project stage to $group , since $group is mainly used by accumulator .

In the previous $group pipeline, you can group documents using a formatted date key, aggregate using the $avg operator, and return the month as an integer from the previous pipeline using $first .

Starting the next aggregation pipeline should give you the desired result:

 db.collection.aggregate([ { "$project": { "formattedDate": { "$dateToString": { "format": "%Y-%m-%d", "date": "$ceatedAt" } }, "createdAtMonth": { "$month": "$ceatedAt" }, "rating": 1 } }, { "$group": { "_id": "$formattedDate", "average": { "$avg": "$rating" }, "month": { "$first": "$createdAtMonth" }, } } ]) 

Answer 2

For the collection, use aggregate() to $group , grouping _id of $month from the ceatedAt (sic) field of your documents and make the month field in the output document equal to this value.

Then create a field called ceatedAt that uses the $dateToString to format field $date entered document as "%Y-%m-%d" .

Finally, create a field called average , that is, $avg the $rating field of the entered 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" } } ])