Group and query by month and day in Mongo DB

Question

I have collection named "orders" and in its documents I have attributes _id, totalAmount and orderTime. I want to get the total amount of orders in a given month ordered by day. Here is the MySQL query for that.(Assuming given month is 9)

SELECT EXTRACT(DAY FROM orderTime) as day, SUM(totalAmount)
FROM orders
where EXTRACT(MONTH FROM orderTime)=9
GROUP BY day;

I need a Mongo query which performs exactly same task. Is there anyway I can achieve this?

Answer 1

Assuming orderTime is a ISODate field, You need to use the aggregation pipeline as below:

• Project a field for each record to depict its day and month values.
• Match all the records whose month value = 9.
• Group by the day field and calculate the sum of the totalAmount field.
• Then project the required fields.

The Code:

db.orders.aggregate([
{$project:{"totalAmount":1,
           "day":{$dayOfMonth:"$orderTime"},
           "month":{$month:"$orderTime"}}},
{$match:{"month":9}},
{$group:{"_id":"$day",
         "sum":{$sum:"$totalAmount"},
         "day":{$first:"$day"}}},
{$project:{"_id":0,
           "sum":1,
           "day":1}}
])