Mongo/Mongoose汇总-$ redact和$ cond问题 [英] Mongo / Mongoose Aggregation - $redact and $cond issues
问题描述
我很幸运地获得了另一个SO问题的真棒答案 Mongo/Mongoose-汇总依据@chridam中的日期，其中给出了一系列文件，例如:
I was fortunate enough to get an awesome answer to another SO question Mongo / Mongoose - Aggregating by Date from @chridam which given a set of documents like:
{ "_id" : ObjectId("5907a5850b459d4fdcdf49ac"), "amount" : -33.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-04-26T23:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.581Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49ba"), "amount" : -61.3, "name" : "Amazon", "method" : "VIS", "date" : ISODate("2017-03-23T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.592Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49ce"), "amount" : -3.3, "name" : "Tesco", "method" : "VIS", "date" : ISODate("2017-03-15T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.601Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49cc"), "amount" : -26.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-03-16T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.600Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49f7"), "amount" : -63.3, "name" : "Sky", "method" : "VIS", "date" : ISODate("2017-03-02T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.617Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49be"), "amount" : -3.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-03-22T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.593Z"), "category" : "Not Set", "__v" : 0 }
需要一个查询，该查询将按供应商，年，月和周汇总支出.该查询在下面，几乎可以正常运行，但是当我在应用程序中使用它时，我注意到了一个严重的问题
required a query that would aggregate the spend by vendor, year, month and week. The query is below and it almost works fantastically but as I have used it in my application I have noticed a significant problem
db.statements.aggregate([
{ "$match": { "name": "RINGGO" } },
{
"$redact": {
"$cond": [
{
"$and": [
{ "$eq": [{ "$year": "$date" }, 2017 ]}, // within my route this uses parseInt(req.params.year)
{ "$eq": [{ "$month": "$date" }, 3 ]}, // within my route this uses parseInt(req.params.month)
{ "$eq": [{ "$week": "$date" }, 12 ]} // within my route this uses parseInt(req.params.week)
]
},
"$$KEEP",
"$$PRUNE"
]
}
},{
"$group": {
"_id": {
"name": "$name",
"year": { "$year": "$date" },
"month": { "$month": "$date" },
"week": { "$week": "$date" }
},
"total": { "$sum": "$amount" }
}
},
{
"$group": {
"_id": {
"name": "$_id.name",
"year": "$_id.year"
},
"YearlySpends": { "$push": "$total" },
"totalYearlyAmount": { "$sum": "$total" },
"data": { "$push": "$$ROOT" }
}
},
{ "$unwind": "$data" },
{
"$group": {
"_id": {
"name": "$_id.name",
"month": "$data._id.month"
},
"YearlySpends": { "$first": "$YearlySpends" },
"totalYearlyAmount": { "$first": "$totalYearlyAmount" },
"MonthlySpends": { "$push": "$data.total" },
"totalMonthlyAmount": { "$sum": "$data.total" },
"data": { "$push": "$data" }
}
},
{ "$unwind": "$data" },
{
"$group": {
"_id": {
"name": "$_id.name",
"week": "$data._id.week"
},
"YearlySpends": { "$first": "$YearlySpends" },
"totalYearlyAmount": { "$first": "$totalYearlyAmount" },
"MonthlySpends": { "$first": "$MonthlySpends" },
"totalMonthlyAmount": { "$first": "$totalMonthlyAmount" },
"WeeklySpends": { "$push": "$data.total" },
"totalWeeklyAmount": { "$sum": "$data.total" },
"data": { "$push": "$data" }
}
},
{ "$unwind": "$data" },
{
"$group": {
"_id": "$data._id",
"YearlySpends": { "$first": "$YearlySpends" },
"totalYearlyAmount": { "$first": "$totalYearlyAmount" },
"MonthlySpends": { "$first": "$MonthlySpends" },
"totalMonthlyAmount": { "$first": "$totalMonthlyAmount" },
"WeeklySpends": { "$first": "$WeeklySpends" },
"totalWeeklyAmount": { "$first": "$totalWeeklyAmount" }
}
}
])
运行此查询将返回
{ "_id" :
{ "name" : "RINGGO",
"year" : 2017,
"month" : 3,
"week" : 12 },
"YearlySpends" : [ -9.6 ],
"totalYearlyAmount" : -9.6,
"MonthlySpends" : [ -9.6 ],
"totalMonthlyAmount" : -9.6,
"WeeklySpends" : [ -9.6 ],
"totalWeeklyAmount" : -9.6
}
当我更改为想查看该月的支出时
And when I change to wanting to see the month's spending
"$cond": [
{
"$and": [
{ "$eq": [{ "$year": "$date" }, 2017 ]},
{ "$eq": [{ "$month": "$date" }, 3 ]}
]
},
"$$KEEP",
"$$PRUNE"
]
我得到:
{ "_id" : { "name" : "RINGGO", "year" : 2017, "month" : 3, "week" : 12 }, "YearlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalYearlyAmount" : -25.799999999999997, "MonthlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalMonthlyAmount" : -25.799999999999997, "WeeklySpends" : [ -9.6 ], "totalWeeklyAmount" : -9.6 }
{ "_id" : { "name" : "RINGGO", "year" : 2017, "month" : 3, "week" : 9 }, "YearlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalYearlyAmount" : -25.799999999999997, "MonthlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalMonthlyAmount" : -25.799999999999997, "WeeklySpends" : [ -3.3 ], "totalWeeklyAmount" : -3.3 }
{ "_id" : { "name" : "RINGGO", "year" : 2017, "month" : 3, "week" : 11 }, "YearlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalYearlyAmount" : -25.799999999999997, "MonthlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalMonthlyAmount" : -25.799999999999997, "WeeklySpends" : [ -9.6 ], "totalWeeklyAmount" : -9.6 }
{ "_id" : { "name" : "RINGGO", "year" : 2017, "month" : 3, "week" : 13 }, "YearlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalYearlyAmount" : -25.799999999999997, "MonthlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalMonthlyAmount" : -25.799999999999997, "WeeklySpends" : [ -3.3 ], "totalWeeklyAmount" : -3.3 }
但是，当我运行简单的db.statements.find({"name":"RINGGO"})时，我得到:
However when I run a simple db.statements.find({"name":"RINGGO"}) I get:
{ "_id" : ObjectId("5907a5850b459d4fdcdf49ac"), "amount" : -3.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-03-26T23:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.581Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49ba"), "amount" : -6.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-03-23T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.592Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49ce"), "amount" : -3.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-03-15T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.601Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49cc"), "amount" : -6.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-03-16T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.600Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49f7"), "amount" : -3.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-03-02T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.617Z"), "category" : "Not Set", "__v" : 0 }
{ "_id" : ObjectId("5907a5850b459d4fdcdf49be"), "amount" : -3.3, "name" : "RINGGO", "method" : "VIS", "date" : ISODate("2017-03-22T00:00:00Z"), "importDate" : ISODate("2017-05-01T21:15:49.593Z"), "category" : "Not Set", "__v" : 0 }
因此，您可以看到与上一个按名称查找的输出相比，上一个输出中的MonthlySpends中的项目数量有所不同.此外，您还可以看到某些值在不应该加在一起的情况下在MonthlySpends中被求和了.
So you can see that there is a different number of items in MonthlySpends in previous output compared to that shown in the output from the find by name. Also you can see that some of the values are being summed together in MonthlySpends when they shouldn't be.
理想情况下，我希望得到一个输出:
当我的$redact包含:
Ideally I'm looking to get to an output which:
when I have $redact containing:
"$cond": [
{
"$and": [
{ "$eq": [{ "$year": "$date" }, 2017 ]},
{ "$eq": [{ "$month": "$date" }, 3 ]},
{ "$eq": [{ "$week": "$date" }, 12 ]}
]
},
"$$KEEP",
"$$PRUNE"
]
返回
{ "_id" : { "name" : "RINGGO", "year" : 2017, "month" : 3, "week" : 12 }, "YearlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalYearlyAmount" : -25.799999999999997, "MonthlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalMonthlyAmount" : -25.799999999999997, "WeeklySpends" : [ -9.6 ], "totalWeeklyAmount" : -9.6 }
当我的$redact包含:
"$cond": [
{
"$and": [
{ "$eq": [{ "$year": "$date" }, 2017 ]},
{ "$eq": [{ "$month": "$date" }, 3 ]},
]
},
"$$KEEP",
"$$PRUNE"
]
返回
{ "_id" : { "name" : "RINGGO", "year" : 2017, "month" : 3 }, "YearlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalYearlyAmount" : -25.799999999999997, "MonthlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalMonthlyAmount" : -25.799999999999997 }
当我有$redact包含:
"$cond": [
{
"$and": [
{ "$eq": [{ "$year": "$date" }, 2017 ]}
]
},
"$$KEEP",
"$$PRUNE"
]
返回
{ "_id" : { "name" : "RINGGO", "year" : 2017 }, "YearlySpends" : [ -3.3, -9.6, -9.6, -3.3 ], "totalYearlyAmount" : -25.799999999999997}
在此非常需要的任何帮助.我尝试修改查询，但恐怕我对它的理解不足以正确修改它.
Any help in this much required. I've tried tinkering with the query but I'm afraid I just don't understand it enough to modify it correctly.
我的Mongoose版本是^4.9.5，而我的mongo是3.4.2.
My Mongoose version is ^4.9.5 and my mongo is 3.4.2.
推荐答案
您可以在3.4版本中尝试将$facet与$addFields进行并行聚合.
You can try $facet with $addFields for parallel aggregation in 3.4 version.
这将降低整体复杂性，并且您可以同时使用其自己的匹配输入来运行分组.
This will reduce the overall complexity and you can run groupings with its own matching input at the same time.
下面的代码基于请求对象动态构建聚合管道.
The below code builds the aggregation pipeline dynamically based on request object.
// Sample request
var request = {
"name":"RINGGO",
"year": 2017,
"month":3,
"week":12
};
// Build initial match document on name
var match1 = {
name: request["name"]
};
// Build project & facet document for date based aggregation
var addFields = {};
var facet = {};
// Add year followed by year facet
if (request["year"]) {
addFields["year"] = { "$year": "$date" },
facet["Yearly"] =
[
{
"$match":{ "year": request["year"] }
},
{
"$group": {
"_id": {
"name": "$name",
"year": "$year"
},
"spend": { "$push":"$amount" },
"total": { "$sum": "$amount" }
}
}
];
}
// Add month followed by month facet
if (request["month"]) {
addFields["month"] = { "$month": "$date" };
facet["Monthly"] =
[
{
"$match":{ "month": request["month"] }
},
{
"$group": {
"_id": {
"name": "$name",
"month": "$month"
},
"spend": { "$push":"$amount" },
"total": { "$sum": "$amount" }
}
}
];
}
// Add week followed by week facet
if (request["week"]) {
addFields["week"] = { "$week": "$date" };
facet["Weekly"] =
[
{
"$match":{ "week": request["week"] }
},
{
"$group": {
"_id": {
"name": "$name",
"week": "$week"
},
"spend": { "$push":"$amount" },
"total": { "$sum": "$amount" }
}
}
];
}
// Use aggregate builder
statements.aggregate()
.match(match1)
.append({"$addFields": addFields}) // No addFields stage in mongoose builder
.facet(facet)
.exec(function(err, data) {});
name/year/month/week条件的Mongo Shell查询.
Mongo Shell Query for name/year/month/week criteria.
db.statements.aggregate({
'$match': {
name: 'RINGGO'
}
}, {
'$addFields': {
year: {
'$year': '$date'
},
month: {
'$month': '$date'
},
week: {
'$week': '$date'
}
}
}, {
'$facet': {
Yearly: [{
'$match': {
year: 2017
}
},
{
'$group': {
_id: {
name: '$name',
year: '$year'
},
spend: {
'$push': '$amount'
},
total: {
'$sum': '$amount'
}
}
}
],
Monthly: [{
'$match': {
month: 3
}
},
{
'$group': {
_id: {
name: '$name',
month: '$month'
},
spend: {
'$push': '$amount'
},
total: {
'$sum': '$amount'
}
}
}
],
Weekly: [{
'$match': {
week: 12
}
},
{
'$group': {
_id: {
name: '$name',
week: '$week'
},
spend: {
'$push': '$amount'
},
total: {
'$sum': '$amount'
}
}
}
]
}
})
样品响应
{
"Yearly": [{
"_id": {
"name": "RINGGO",
"year": 2017
},
"spend": [-3.3, -6.3, -3.3, -6.3, -3.3, -3.3],
"total": -25.799999999999997
}],
"Monthly": [{
"_id": {
"name": "RINGGO",
"month": 3
},
"spend": [-3.3, -6.3, -3.3, -6.3, -3.3, -3.3],
"total": -25.799999999999997
}],
"Weekly": [{
"_id": {
"name": "RINGGO",
"week": 12
},
"spend": [-6.3, -3.3],
"total": -9.6
}]
}
您可以对Year/Month和Year输入值运行类似的聚合.
You can run the similar aggregation for Year/Month and Year input values.
因此您可以看到其中有不同数量的项目 与上一个输出相比，前一个输出中的每月支出 从名称中查找.您还可以看到一些值是 在不应该的月度支出中汇总在一起.
So you can see that there is a different number of items in MonthlySpends in previous output compared to that shown in the output from the find by name. Also you can see that some of the values are being summed together in MonthlySpends when they shouldn't be.
这发生在$group 1中，其中$week聚合将第11周的两个日期[15，16]的每个汇总到第11周，然后将其他两个日期[22，23]的汇总到12的第二周，以显示为总计在MonthySpends中.
This happens in $group 1 where $week aggregation rolls up each of two dates [15, 16] amount into week 11 and other two dates [22, 23] amount into week 12 later to show up as summed totals in MonthySpends.
这篇关于Mongo/Mongoose汇总-$ redact和$ cond问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
