在 mongoDB 中使用更新聚合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19384871/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Aggregation with update in mongoDB
提问by Sam
I've a collection with many similar structured document, two of the document looks like
我有一个包含许多类似结构化文档的集合，其中两个文档看起来像
Input:
输入：
{
"_id": ObjectId("525c22348771ebd7b179add8"),
"cust_id": "A1234",
"score": 500,
"status": "A"
"clear": "No"
}
{
"_id": ObjectId("525c22348771ebd7b179add9"),
"cust_id": "A1234",
"score": 1600,
"status": "B"
"clear": "No"
}
By default the clearfor all document is "No",
默认情况下clear，所有文档都是"No"，
Req: I have to add the score of all documents with same cust_id, provided they belong to status"A"and status"B". If the scoreexceeds 2000then I have to update the clearattribute to "Yes"for all of the document with the same cust_id.
要求：我必须添加具有相同 的所有文档的分数cust_id，前提是它们属于status"A"和status"B"。如果score超过2000那么我不得不更新clear属性"Yes"对所有具有相同的文件cust_id。
Expected output:
预期输出：
{
"_id": ObjectId("525c22348771ebd7b179add8"),
"cust_id": "A1234",
"score": 500,
"status": "A"
"clear": "Yes"
}
{
"_id": ObjectId("525c22348771ebd7b179add9"),
"cust_id": "A1234",
"score": 1600,
"status": "B"
"clear": "Yes"
}
Yes because 1600+500 = 2100, and 2100 > 2000.
是的，因为 1600+500 = 2100，并且 2100 > 2000。
My Approach: I was only able to get the sum by aggregate function but failed at updating
我的方法：我只能通过聚合函数获得总和但更新失败
db.aggregation.aggregate([
{$match: {
$or: [
{status: 'A'},
{status: 'B'}
]
}},
{$group: {
_id: '$cust_id',
total: {$sum: '$score'}
}},
{$match: {
total: {$gt: 2000}
}}
])
Please suggest me how do I proceed.
请建议我如何进行。
采纳答案by Sam
After a lot of trouble, experimenting mongo shell I've finally got a solution to my question.
在经历了很多麻烦之后，尝试使用 mongo shell 我终于找到了我的问题的解决方案。
Psudocode:
伪代码：
# To get the list of customer whose score is greater than 2000
cust_to_clear=db.col.aggregate(
{$match:{$or:[{status:'A'},{status:'B'}]}},
{$group:{_id:'$cust_id',total:{$sum:'$score'}}},
{$match:{total:{$gt:500}}})
# To loop through the result fetched from above code and update the clear
cust_to_clear.result.forEach
(
function(x)
{
db.col.update({cust_id:x._id},{$set:{clear:'Yes'}},{multi:true});
}
)
Please comment, if you have any different solution for the same question.
请评论，如果您对同一问题有任何不同的解决方案。
回答by Mitar
With Mongo 4.2 it is now possible to do this using update with aggregation pipeline. The example 2 has example how you do conditional updates:
在 Mongo 4.2 中，现在可以使用带有聚合管道的更新来做到这一点。示例 2 提供了如何进行条件更新的示例：
db.runCommand(
{
update: "students",
updates: [
{
q: { },
u: [
{ $set: { average : { $avg: "$tests" } } },
{ $set: { grade: { $switch: {
branches: [
{ case: { $gte: [ "$average", 90 ] }, then: "A" },
{ case: { $gte: [ "$average", 80 ] }, then: "B" },
{ case: { $gte: [ "$average", 70 ] }, then: "C" },
{ case: { $gte: [ "$average", 60 ] }, then: "D" }
],
default: "F"
} } } }
],
multi: true
}
],
ordered: false,
writeConcern: { w: "majority", wtimeout: 5000 }
}
)
Another example:
另一个例子：
db.c.update({}, [
{$set:{a:{$cond:{
if: {}, // some condition
then:{} , // val1
else: {} // val2 or "$$REMOVE" to not set the field or "$a" to leave existing value
}}}}
]);
回答by SuperAce99
You need to do this in two steps:
您需要分两步执行此操作：
- Identify customers (
cust_id) with a total score greater than 200 - For each of these customers, set
cleartoYes
- 识别
cust_id总分大于 200 的客户 ( ) - 对于这些客户中的每一个，设置
clear为Yes
You already have a good solution for the first part. The second part should be implemented as a separate update()calls to the database.
对于第一部分，您已经有了一个很好的解决方案。第二部分应该作为update()对数据库的单独调用来实现。
Psudocode:
伪代码：
# Get list of customers using the aggregation framework
cust_to_clear = db.col.aggregate(
{$match:{$or:[{status:'A'},{status:'B'}]}},
{$group:{_id:'$cust_id', total:{$sum:'$score'}}},
{$match:{total:{$gt:2000}}}
)
# Loop over customers and update "clear" to "yes"
for customer in cust_to_clear:
id = customer[_id]
db.col.update(
{"_id": id},
{"$set": {"clear": "Yes"}}
)
This isn't ideal because you have to make a database call for every customer. If you need to do this kind of operation often, you might revise your schema to include the total score in each document. (This would have to be maintained by your application.) In this case, you could do the update with a single command:
这并不理想，因为您必须为每个客户进行数据库调用。如果您需要经常进行此类操作，您可以修改您的架构以在每个文档中包含总分。（这必须由您的应用程序维护。）在这种情况下，您可以使用单个命令进行更新：
db.col.update(
{"total_score": {"$gt": 2000}},
{"$set": {"clear": "Yes"}},
{"multi": true}
)
回答by Yann Moisan
In MongoDB 2.6., it will be possible to write the output of aggregation query, with the same command.
在 MongoDB 2.6. 中，可以使用相同的命令编写聚合查询的输出。
More information here : http://docs.mongodb.org/master/reference/operator/aggregation/out/
更多信息：http: //docs.mongodb.org/master/reference/operator/aggregation/out/
回答by Logan Cundiff
Short Answer: To avoid looping a Database query, just add $outto the end and specify your collection like so:
简短回答：为避免循环数据库查询，只需在末尾添加$out并指定您的集合，如下所示：
db.aggregation.aggregate([
{$match: {
$or: [
{status: 'A'},
{status: 'B'}
]
}},
{$group: {
_id: '$cust_id',
total: {$sum: '$score'}
}},
{$match: {
total: {$gt: 2000}
}},
{ $out: "<collection name here>"}
])
Elaboration: The current solution is looping through a database query, which is not good time efficiency wise and also a lot more code. Mitar's answer is not updating through an aggregation, but the opposite => using an aggregation within Mongo's update. If your wondering what is a pro in doing it this way, well you can use all of the aggregation pipeline as opposed to being restricted to only a few as specified in their documentation.
详细说明：当前的解决方案是通过数据库查询进行循环，这在时间效率方面不是很好，而且代码也很多。Mitar 的答案不是通过聚合更新，而是相反 => 在 Mongo 的更新中使用聚合。如果您想知道这样做的专业人士是什么，那么您可以使用所有聚合管道，而不是仅限于其文档中指定的少数几个。
Here is an example of an aggregate that won't work with Mongo's update:
这是一个不适用于 Mongo 更新的聚合示例：
db.getCollection('foo').aggregate([
{ $addFields: {
testField: {
$in: [ "someValueInArray", '$arrayFieldInFoo']
}
}},
{ $out : "foo" }]
)
This will output the updated collection with a new test field that will be true if "someValueInArray" is in "arrayFieldInFoo" or false otherwise. This is NOTpossible currently with Mongo.update since $in cannot be used inside update aggregate.
这将输出带有新测试字段的更新集合，如果“someValueInArray”在“arrayFieldInFoo”中，则该字段为真，否则为假。这是不是可能与目前自Mongo.update在$更新汇总中不能使用。
Lastly, keep in mind syntax can be different for shell commands and drivers like the NodeJs Mongo driver.
最后，请记住，shell 命令和驱动程序（如 NodeJs Mongo 驱动程序）的语法可能不同。
