Menu
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
695
MongoDB $count Aggregation First, we invoke the $count operator and then specify the string. In this syntax, 'the_string' represents the label or the name of the output field. It must be non-empty and cannot start with a dollar sign '$' or dot '. ' character.
The $dateDiff expression returns the integer difference between the startDate and endDate measured in the specified units . Durations are measured by counting the number of times a unit boundary is passed. For example, two dates that are 18 months apart would return 1 year difference instead of 1.5 years .
You can specify a particular date by passing an ISO-8601 date string with a year within the inclusive range 0 through 9999 to the new Date() constructor or the ISODate() function. These functions accept the following formats: new Date("<YYYY-mm-dd>") returns the ISODate with the specified date.
On large collections of millions of documents, MongoDB's aggregation was shown to be much worse than Elasticsearch. Performance worsens with collection size when MongoDB starts using the disk due to limited system RAM. The $lookup stage used without indexes can be very slow.
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
85
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
