MongoDB update an array element matching _id and multiple conditions within the array

Question

I have a MongoDB collections as:

Collection:

[
  {
    "_id": ObjectId("64390a7196f544082a671469"),
    "name": "Sydney",
    "issues": [
      {"vol": "2", "num": "1", "freq": 5},
      {"vol": "1", "num": "2", "freq": 7}
    ]
  },
  {
    "_id": ObjectId("64390a8796f544082a67146a"),
    "name": "Canton",
    "issues": [
      {"vol": "2", "num": "2", "freq": 9},
      {"vol": "1", "num": "3", "freq": 9},
      {"vol": "2", "num": "1", "freq": 11}
    ]
  }
]

I want to update issues.freq field matching with the _id, issues.vol and issues.num values.

To be specific, if

1. _id:ObjectId("64390a8796f544082a67146a"),
2. issue.vol:"2" and
3. issue.num:"1"

issue.freq will be updated to 45 (from existing value of 11).

Here is the query that I have arrived so far:

Query:

db.collection.update({
  "_id": ObjectId("64390a8796f544082a67146a"),
  "issues.vol": "2",
  "issues.num": "1"
},
{
  "$set": {
    "issues.$.freq": 45
  }
})

The problem is, it updates the value for ("_id": ObjectId("64390a8796f544082a67146a"), "issues.vol": "2" and "issues.num": "2") instead of ("_id": ObjectId("64390a8796f544082a67146a"), "issues.vol": "2" and "issues.num": "1"). What am I doing wrong?

Result set (after my query above):

[
  {
    "_id": ObjectId("64390a7196f544082a671469"),
    "issues": [
      {"freq": 5, "num": "1", "vol": "2"},
      {"freq": 7, "num": "2","vol": "1"}
    ],
    "name": "Sydney"
  },
  {
    "_id": ObjectId("64390a8796f544082a67146a"),
    "issues": [
      {"freq": 45, "num": "2", "vol": "2"},
      {"freq": 9, "num": "3", "vol": "1"},
      {"freq": 11, "num": "1", "vol": "2"}
    ],
    "name": "Canton"
  }
]

Mongo playground link

So many thanks for reading this far. I would also like to extend my gratitude if you have given some thoughts on this.

Answer 1

Works with positional filtered operator $[<identifier>] to update specific element(s) in an array.

db.collection.update({
  "_id": ObjectId("64390a8796f544082a67146a"),
  "issues.vol": "2",
  "issues.num": "1"
},
{
  "$set": {
    "issues.$[i].freq": 45
  }
},
{
  arrayFilters: [
    {
      "i.vol": "2",
      "i.num": "1"
    }
  ]
})

Demo @ Mongo Playground

Answer 2

Corresponding PyMongo query:

If it helps anyone working with PyMongo (Note: arrayFilters of MongoDB becomes array_filters in PyMongo):

import bson

edvol= "2"
ednum= "1"
per_col.update_one(
    {
        '_id': bson.ObjectId("64390a8796f544082a67146a"),
        "issues.vol": edvol,
        "issues.num": ednum
    }, 
    {'$set': {"issues.$[i].freq": 45}}, 
    upsert=False,
    array_filters=[{'i.vol': edvol, 'i.num': ednum}]
)