How to update documents using the value from inner array

Question

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.

something [similar](https://stackoverflow.com/questions/76089325/how-to-toggle-the-boolean-value-in-array-in-mongoose) i got sometime back. i guess the same concept can be applied here as well. i dont know go though — cmgchess, Jul 22 '23 at 15:58
Thanks ; I think this could have worked but less effectively than the solution provided by Zeke Lu — Ezechiel, Jul 23 '23 at 21:36

score 0 · Accepted Answer · answered Jul 22 '23 at 18:39

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"}},
                    },
                },
            },
        },
    },
}

Wonderful ! I was indeed using aggregations but did not know about $let This works like a charm, even implemented in the more complex real code. Thanks very very much ! — Ezechiel, Jul 23 '23 at 21:33

How to update documents using the value from inner array

1 Answers1