Group and query by month and day in Mongo DB
I have a collection named "orders" and in its documents I have the attributes _id, totalAmount and orderTime. I want to receive the total amount of orders in a specific month ordered during the day. Here is a MySQL query for this. (Assuming the 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 that does exactly the same task. Is there anyway I can achieve this?
+3
source to share
1 answer
Assuming orderTime
is a field ISODate
, you need to use an aggregation pipeline as shown below:
-
Project
a field for each record displaying its day and month values. -
Match
all records with month value = 9. -
Group
in the day field and calculate the amounttotalAmount
field. - Then the
Project
required fields.
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}}
])
+2
source to share