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.

Answer 1

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