英文:
How to update documents using the value from inner array
问题
我在一个看起来并不复杂的问题上遇到了困难,也许有些我没有考虑到或者看到的东西。
我有很多包含对象数组的文档,就像这样:
{
"_id": "Ox1",
"results": [
{
"id": "a1",
"somethingElse": "aa",
"value": 1
},
{
"id": "a2",
"somethingElse": "bb",
"value": 2
},
{
"id": "a3",
"somethingElse": "cc",
"value": 3
}
],
"total": 0
},
{
"_id": "Ox2",
"results": [
{
"id": "a1",
"somethingElse": "aa",
"value": 44
},
{
"id": "a4",
"somethingElse": "bb",
"value": 4
},
{
"id": "a5",
"somethingElse": "aa",
"value": 5
}
],
"total": 0
},
{
"_id": "Ox3",
"results": [
{
"id": "a2",
"somethingElse": "aa",
"value": 1
},
{
"id": "a3",
"somethingElse": "aa",
"value": 4
},
{
"id": "a4",
"somethingElse": "aa",
"value": 5
}
],
"total": 0
}
我想要一个UpdateMany查询,更新所有包含以下内容的文档的"results":
- "id": "a1"
- "somethingElse": "aa"
将它们的"total"增加上包含"id": "a1"和"somethingElse": "aa"的"results"的"value"
所以在我们的例子中:
"0x1"有一个包含"id": "a1"和"somethingElse": "aa"的结果,其"value"为1
- 我希望它的"total"增加1
"0x2"有一个包含"id": "a1"和"somethingElse": "aa"的结果,其"value"为44
- 我希望它的"total"增加44
"0x3"不符合条件
这段代码是用Go编写的:
// 这里我只过滤满足条件的文档
filter := bson.D{{
Key: "results",
Value: bson.D{{
Key: "$elemMatch",
Value: bson.D{
{Key: "id", Value: "a1"},
{Key: "somethingElse", Value: "aa"},
}},
}},
}
// 这是关键的部分
addTotal := bson.M{
"$set": bson.D{{
Key: "total",
Value: bson.D{{
Key: "$sum",
Value: bson.A{
"$total",
bson.M{
// 我如何从数组中获取正确对象的"value"?
},
},
}},
}},
}
这个可行吗?
我没有找到关于内部/嵌套查询的太多信息。
英文:
I am getting stuck on something that doesn't seem that complicated, maybe there is something I didn't think about or saw.
Having (a lot) of documents containing an array of objects, like those:
{
"_id": "Ox1",
"results": [
{
"id": "a1",
"somethingElse": "aa",
"value": 1
},
{
"id": "a2",
"somethingElse": "bb",
"value": 2
},
{
"id": "a3",
"somethingElse": "cc",
"value": 3
}
],
"total": 0
},
{
"_id": "Ox2",
"results": [
{
"id": "a1",
"somethingElse": "aa",
"value": 44
},
{
"id": "a4",
"somethingElse": "bb",
"value": 4
},
{
"id": "a5",
"somethingElse": "aa",
"value": 5
}
],
"total": 0
},
{
"_id": "Ox3",
"results": [
{
"id": "a2",
"somethingElse": "aa",
"value": 1
},
{
"id": "a3",
"somethingElse": "aa",
"value": 4
},
{
"id": "a4",
"somethingElse": "aa",
"value": 5
}
],
"total": 0
}
I want an UpdateMany query that updates all the documents having a "results" containing:
- "id": "a1"
- "somethingElse": "aa"
increasing their "total" by the value of the "results" containing "id": "a1" AND "somethingElse": "aa"
So in our example:
"0x1" has a result containing "id": "a1" AND "somethingElse": "aa" having a "value" of 1
-> I want its "total" to be increased by 1
"0x2" has a result containing "id": "a1" AND "somethingElse": "aa" having a "value" of 44
-> I want its "total" to be increased by 44
"0x3" does not meet the condition
Written in Go, this starts like:
// Here I filter only the documents meeting the condition
filter := bson.D{{
Key: "results,
Value: bson.D{{
Key: "$elemMatch",
Value: bson.D{
{Key: "id", Value: "a1"},
{Key: "somethingElse", Value: "aa"},
}},
}},
}
// This is where it gets tricky
addTotal := bson.M{
"$set": bson.D{{
Key: "total",
Value: bson.D{{
Key: "$sum",
Value: bson.A{
"$total",
bson.M{
// How can I get the "value" from the right object from the array ?
},
},
}},
}},
}
Is that even possible?
I have not found much about inner / embedded queries.
答案1
得分: 0
db.collection.updateMany(filter, update, options)
中的update
参数可以是一个更新文档或一个聚合管道(文档)。
更新文档只包含更新操作符表达式,格式如下:
{
"<operator1>": { "<field1>": <value1>, ... },
"<operator2>": { "<field2>": <value2>, ... },
...
}
这些值不能引用文档中的字段。
而聚合管道更为高级,可以引用文档中的字段。以下是使用聚合管道的一种方法:
db.collection.updateMany(
{ results: { $elemMatch: { id: 'a1', somethingElse: 'aa' } } },
[
{
$set: {
total: {
$let: {
vars: {
items: {
$filter: {
input: '$results',
as: 'item',
cond: {
$and: [
{ $eq: ['$$item.id', 'a1'] },
{ $eq: ['$$item.somethingElse', 'aa'] },
],
},
},
},
},
in: { $add: ['$total', { $sum: '$$items.value' }] },
},
},
},
},
]
);
翻译为Go代码:
filter := bson.M{
"results": bson.M{
"$elemMatch": bson.M{
"id": "a1",
"somethingElse": "aa",
},
},
}
vars := bson.M{
"items": bson.M{
"$filter": bson.M{
"input": "$results",
"as": "item",
"cond": bson.M{
"$and": bson.A{
bson.M{"$eq": bson.A{"$$item.id", "a1"}},
bson.M{"$eq": bson.A{"$$item.somethingElse", "aa"}},
},
},
},
},
}
update := bson.A{
bson.M{
"$set": bson.M{
"total": bson.M{
"$let": bson.M{
"vars": vars,
"in": bson.M{
"$add": bson.A{"$total", bson.M{"$sum": "$$items.value"}},
},
},
},
},
},
}
以上是翻译好的内容。
英文:
The update
parameter in db.collection.updateMany(filter, update, options)
could be an update document or an aggregation pipeline (doc).
An update document contains only update operator expressions, which looks like this:
{
<operator1>: { <field1>: <value1>, ... },
<operator2>: { <field2>: <value2>, ... },
...
}
The values can not reference fields in the document.
While an aggregation pipeline is more advance and can reference fields in the document. Here is one way to do it with an aggregation pipeline:
db.collection.updateMany(
{ results: { $elemMatch: { id: 'a1', somethingElse: 'aa' } } },
[
{
$set: {
total: {
$let: {
vars: {
items: {
$filter: {
input: '$results',
as: 'item',
cond: {
$and: [
{ $eq: ['$$item.id', 'a1'] },
{ $eq: ['$$item.somethingElse', 'aa'] },
],
},
},
},
},
in: { $add: ['$total', { $sum: '$$items.value' }] },
},
},
},
},
]
);
Translated into Go code:
filter := bson.M{
"results": bson.M{
"$elemMatch": bson.M{
"id": "a1",
"somethingElse": "aa",
},
},
}
vars := bson.M{
"items": bson.M{
"$filter": bson.M{
"input": "$results",
"as": "item",
"cond": bson.M{
"$and": bson.A{
bson.M{"$eq": bson.A{"$$item.id", "a1"}},
bson.M{"$eq": bson.A{"$$item.somethingElse", "aa"}},
},
},
},
},
}
update := bson.A{
bson.M{
"$set": bson.M{
"total": bson.M{
"$let": bson.M{
"vars": vars,
"in": bson.M{
"$add": bson.A{"$total", bson.M{"$sum": "$$items.value"}},
},
},
},
},
},
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论