MongoDb How to aggregate by month and year
18,697
You have lost expenseAmount
field during projection stage. Simply add it:
{$project : {
month : {$month : "$entryTime"},
year : {$year : "$entryTime"},
expenseAmount : 1
}},
Note that if field does not exist in document, then $sum
returns 0.
Also note that there is another aggregation operator which performs as you expected - $addFields
. It adds new fields to document and preserves all existing fields from the input document. But in this case you need only expenseAmount field
Related videos on Youtube
Comments
-
raju over 1 year
I am new to mongodb and trying to learn Mongodb queries
{ "_id" : ObjectId("59815d4704ca1760a45957ca"), "userEmail" : "[email protected]", "expenseAmount" : 200, "expenseRemark" : "aa", "expenseCategory" : "billing", "entryTime" : ISODate("2017-08-02T05:03:57Z"), "__v" : 0 } { "_id" : ObjectId("59815d5404ca1760a45957cb"), "userEmail" : "[email protected]", "expenseAmount" : 300, "expenseRemark" : "ff", "expenseCategory" : "transport", "entryTime" : ISODate("2017-08-02T05:04:11Z"), "__v" : 0 } { "_id" : ObjectId("5980191d04ca1760a45957cd"), "userEmail" : "[email protected]", "expenseAmount" : 100, "expenseRemark" : "rr", "expenseCategory" : "billing", "entryTime" : ISODate("2017-08-01T06:00:46Z"), "__v" : 0 } { "_id" : ObjectId("5980192604ca1760a45957ce"), "userEmail" : "[email protected]", "expenseAmount" : 200, "expenseRemark" : "qq", "expenseCategory" : "transport", "entryTime" : ISODate("2017-08-01T06:01:03Z"), "__v" : 0 } { "_id" : ObjectId("5980192e04ca1760a45957cf"), "userEmail" : "[email protected]", "expenseAmount" : 470, "expenseRemark" : "ff", "expenseCategory" : "transport", "entryTime" : ISODate("2017-08-01T06:01:11Z"), "__v" : 0 } { "_id" : ObjectId("59816ac004ca1760a45957d0"), "userEmail" : "[email protected]", "expenseAmount" : 500, "expenseRemark" : "raj", "expenseCategory" : "transport", "entryTime" : ISODate("2017-08-02T06:01:26Z"), "__v" : 0 } { "_id" : ObjectId("59816acb04ca1760a45957d1"), "userEmail" : "[email protected]", "expenseAmount" : 100, "expenseRemark" : "pet", "expenseCategory" : "pets", "entryTime" : ISODate("2017-08-02T06:01:37Z"), "__v" : 0 } { "_id" : ObjectId("597d7a9c04ca1760a45957d2"), "userEmail" : "[email protected]", "expenseAmount" : 500, "expenseRemark" : "gt", "expenseCategory" : "sports", "entryTime" : ISODate("2017-07-30T06:20:04Z"), "__v" : 0 } { "_id" : ObjectId("597d7aaa04ca1760a45957d3"), "userEmail" : "[email protected]", "expenseAmount" : 560, "expenseRemark" : "mov", "expenseCategory" : "entertainment", "entryTime" : ISODate("2017-07-30T06:20:14Z"), "__v" : 0 }
I want to get expenseAmount grouped by Year + Month. I tried
`db.expenses.aggregate( {$project : { month : {$month : "$entryTime"}, year : {$year : "$entryTime"} }}, {$group : { _id : {month : "$month" ,year : "$year" }, total : {$sum : "$expenseAmount"} }})`
Which gives
{ "_id" : { "month" : 7, "year" : 2017 }, "total" : 0 } { "_id" : { "month" : 8, "year" : 2017 }, "total" : 0 }
Please guide me how can I get aggregated result. I could not figure out the way to do that.
Thanks