如何使用内部数组的值更新文档

huangapple go评论89阅读模式
英文:

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:

{
   &lt;operator1&gt;: { &lt;field1&gt;: &lt;value1&gt;, ... },
   &lt;operator2&gt;: { &lt;field2&gt;: &lt;value2&gt;, ... },
   ...
}

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: &#39;a1&#39;, somethingElse: &#39;aa&#39; } } },
  [
    {
      $set: {
        total: {
          $let: {
            vars: {
              items: {
                $filter: {
                  input: &#39;$results&#39;,
                  as: &#39;item&#39;,
                  cond: {
                    $and: [
                      { $eq: [&#39;$$item.id&#39;, &#39;a1&#39;] },
                      { $eq: [&#39;$$item.somethingElse&#39;, &#39;aa&#39;] },
                    ],
                  },
                },
              },
            },
            in: { $add: [&#39;$total&#39;, { $sum: &#39;$$items.value&#39; }] },
          },
        },
      },
    },
  ]
);

Translated into Go code:

filter := bson.M{
	&quot;results&quot;: bson.M{
		&quot;$elemMatch&quot;: bson.M{
			&quot;id&quot;:            &quot;a1&quot;,
			&quot;somethingElse&quot;: &quot;aa&quot;,
		},
	},
}

vars := bson.M{
	&quot;items&quot;: bson.M{
		&quot;$filter&quot;: bson.M{
			&quot;input&quot;: &quot;$results&quot;,
			&quot;as&quot;:    &quot;item&quot;,
			&quot;cond&quot;: bson.M{
				&quot;$and&quot;: bson.A{
					bson.M{&quot;$eq&quot;: bson.A{&quot;$$item.id&quot;, &quot;a1&quot;}},
					bson.M{&quot;$eq&quot;: bson.A{&quot;$$item.somethingElse&quot;, &quot;aa&quot;}},
				},
			},
		},
	},
}

update := bson.A{
	bson.M{
		&quot;$set&quot;: bson.M{
			&quot;total&quot;: bson.M{
				&quot;$let&quot;: bson.M{
					&quot;vars&quot;: vars,
					&quot;in&quot;: bson.M{
						&quot;$add&quot;: bson.A{&quot;$total&quot;, bson.M{&quot;$sum&quot;: &quot;$$items.value&quot;}},
					},
				},
			},
		},
	},
}

huangapple
  • 本文由 发表于 2023年7月22日 22:39:25
  • 转载请务必保留本文链接:https://go.coder-hub.com/76744325.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定