MongoDb如何按月和年汇总 [英] MongoDb How to aggregate by month and year
问题描述
我是mongodb的新手,正在尝试学习Mongodb查询
I am new to mongodb and trying to learn Mongodb queries
{
"_id" : ObjectId("59815d4704ca1760a45957ca"),
"userEmail" : "lk@gmail.com",
"expenseAmount" : 200,
"expenseRemark" : "aa",
"expenseCategory" : "billing",
"entryTime" : ISODate("2017-08-02T05:03:57Z"),
"__v" : 0
}
{
"_id" : ObjectId("59815d5404ca1760a45957cb"),
"userEmail" : "lk@gmail.com",
"expenseAmount" : 300,
"expenseRemark" : "ff",
"expenseCategory" : "transport",
"entryTime" : ISODate("2017-08-02T05:04:11Z"),
"__v" : 0
}
{
"_id" : ObjectId("5980191d04ca1760a45957cd"),
"userEmail" : "lk@gmail.com",
"expenseAmount" : 100,
"expenseRemark" : "rr",
"expenseCategory" : "billing",
"entryTime" : ISODate("2017-08-01T06:00:46Z"),
"__v" : 0
}
{
"_id" : ObjectId("5980192604ca1760a45957ce"),
"userEmail" : "lk@gmail.com",
"expenseAmount" : 200,
"expenseRemark" : "qq",
"expenseCategory" : "transport",
"entryTime" : ISODate("2017-08-01T06:01:03Z"),
"__v" : 0
}
{
"_id" : ObjectId("5980192e04ca1760a45957cf"),
"userEmail" : "lk@gmail.com",
"expenseAmount" : 470,
"expenseRemark" : "ff",
"expenseCategory" : "transport",
"entryTime" : ISODate("2017-08-01T06:01:11Z"),
"__v" : 0
}
{
"_id" : ObjectId("59816ac004ca1760a45957d0"),
"userEmail" : "lk@gmail.com",
"expenseAmount" : 500,
"expenseRemark" : "raj",
"expenseCategory" : "transport",
"entryTime" : ISODate("2017-08-02T06:01:26Z"),
"__v" : 0
}
{
"_id" : ObjectId("59816acb04ca1760a45957d1"),
"userEmail" : "lk@gmail.com",
"expenseAmount" : 100,
"expenseRemark" : "pet",
"expenseCategory" : "pets",
"entryTime" : ISODate("2017-08-02T06:01:37Z"),
"__v" : 0
}
{
"_id" : ObjectId("597d7a9c04ca1760a45957d2"),
"userEmail" : "lk@gmail.com",
"expenseAmount" : 500,
"expenseRemark" : "gt",
"expenseCategory" : "sports",
"entryTime" : ISODate("2017-07-30T06:20:04Z"),
"__v" : 0
}
{
"_id" : ObjectId("597d7aaa04ca1760a45957d3"),
"userEmail" : "lk@gmail.com",
"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"}
}})`
哪个给
{ "_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.
谢谢
推荐答案
在投影阶段您丢失了expenseAmount
字段.只需添加它:
You have lost expenseAmount
field during projection stage. Simply add it:
{$project : {
month : {$month : "$entryTime"},
year : {$year : "$entryTime"},
expenseAmount : 1
}},
请注意,如果文档中不存在该字段,则 $sum
返回0.
Note that if field does not exist in document, then $sum
returns 0.
还要注意,还有另一个聚合运算符可以按您的预期执行- $addFields
.它向文档添加新字段,并保留输入文档中的所有现有字段.但是在这种情况下,您只需要ExpenseAmount字段
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
这篇关于MongoDb如何按月和年汇总的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!