mongodb - arrayFilters with multiple identifier and mixed conditions (AND/OR)

Question

Im trying to do a mongodb query that match some nested array conditions without success...

Documents:

[
   {
      "_id":1,
      "name":"foo",
      "games":[
         {
            "name":"Game1",
            "data":[
               { "data_id":1, "date":"YYYMMDD" },
               { "data_id":2, "date":"YYYMMDD" }
            ]
         },
         {
            "name":"Game2",
            "data":[
               { "data_id":1, "date":"YYYMMDD" },
               { "data_id":2, "date":"YYYMMDD" }
            ]
         }
      ]
   },
   {
      "_id":2,
      "name":"bar",
      "games":[
         {
            "name":"Game2",
            "data":[ 
               { "data_id":1, "date":"YYYMMDD" },
               { "data_id":2, "date":"YYYMMDD" }
            ]
         }
      ]
   }
]

And i want to do an update_many, matching the following data:

all documents
AND ( (_id = 1 and games.name = 'Game1' and games.data.data_id = 1 ) OR (_id = 2 and games.name = 'Game2' and games.data.data_id = 1 ) )
set valid = True

So, for each document i want to update a specific games.data.data_id element.

What i tried

query = {}
update = {"$set": {"games.$[i].data.$[j].valid": True}}
array_filters = [{'$or': [{'_id': 1, 'i.name': 'Game1', 'j.data_id': 1}, {'_id': 2, 'i.name': 'Game2', 'j.data_id': 1}]}]

But it returns an error:

WriteError: Error parsing array filter :: caused by :: Expected a single top-level field name, found 'i' and 'j'

The expected output is:

[
   {
      "_id":1,
      "name":"foo",
      "games":[
         {
            "name":"Game1",
            "data":[
               { "data_id":1, "date":"YYYMMDD", "valid": True },
               { "data_id":2, "date":"YYYMMDD" }
            ]
         },
         {
            "name":"Game2",
            "data":[
               { "data_id":1, "date":"YYYMMDD" },
               { "data_id":2, "date":"YYYMMDD" }
            ]
         }
      ]
   },
   {
      "_id":2,
      "name":"bar",
      "games":[
         {
            "name":"Game2",
            "data":[ 
               { "data_id":1, "date":"YYYMMDD", "valid": True },
               { "data_id":2, "date":"YYYMMDD" }
            ]
         }
      ]
   }
]

So, what am i doing wrong? How to do this? I have tried several other choices, like combining $or and $and, but every time that i use multiple identifiers on same condition, it fails

Answer 1

According to the rules of using arrayFilters, you cannot use the conditions as you are intending, e.g., the _id field within arrayFilters. See Specify arrayFilters for Array Update Operations

• In the update document, use the $[] filtered positional operator to define an identifier, which you then reference in the array filter documents. You cannot have an array filter document for an identifier if the identifier is not included in the update document.
• You can include the same identifier multiple times in the update document; however, for each distinct identifier ($[identifier]) in the update document, you must specify exactly one corresponding array filter document. That is, you cannot specify multiple array filter documents for the same identifier.

Here are other ways of updating; but these cannot use arrayFilters the way you wanted.

1. Using Bulk.find.arrayFilters:

var bulk = db.games.initializeUnorderedBulkOp();
bulk.find( { _id: 1 } )
    .arrayFilters( [ { "g.name": "Game1" }, { "d.data_id": 1 } ] )
    .updateOne( { $set: { "games.$[g].data.$[d].valid": true } } );
bulk.find( { _id: 2 } )
    .arrayFilters( [ { "g.name": "Game2" }, { "d.data_id": 1 } ] )
    .updateOne( { $set: { "games.$[g].data.$[d].valid": true } } );
bulk.execute();

More details at: Bulk.find.arrayFilters

2. Update with Aggregation Pipeline

Starting in MongoDB 4.2, the db.collection.updateMany() method can accept an aggregation pipeline [ <stage1>, <stage2>, ... ] that specifies the modifications to perform. Details at: Update with Aggregation Pipeline

db.games.updateMany(
  {
      $or: [ { $and: [ { _id: 1 }, { "games.name": "Game1" }, { "games.data.data_id": 1 } ] }, 
               { $and: [ { _id: 2 }, { "games.name": "Game2" }, { "games.data.data_id": 1 } ] } 
      ]
  },
  [
    { 
      $set: { 
          games: { 
              $map: { 
                  input: "$games", as: "g",
                  in: { 
                      $mergeObjects: [ 
                          "$$g", 
                          { data: { 
                               $map: { 
                                   input: "$$g.data", as: "d",
                                   in: { 
                                       $cond: [
                                           { $or: [ 
                                               { $and: [ { $eq: [ "$_id", 1 ] }, { $eq: [ "$$g.name", "Game1" ] }, { $eq: [ "$$d.data_id", 1 ] } ] },
                                               { $and: [ { $eq: [ "$_id", 2 ] }, { $eq: [ "$$g.name", "Game2" ] }, { $eq: [ "$$d.data_id", 1 ] } ] }
                                            ] }, 
                                            { $mergeObjects: [ "$$d", { valid: true } ] }, 
                                            "$$d"
                                       ]
                                   }
                               }
                          } }
                      ]
                  }
              }
          }
      }
    } 
  ]
)

3. Aggregation Pipeline and Update

This aggregation + update works with MongoDB versions earlier than 4.2.

db.games.aggregate( [
  { 
      $addFields: { 
          games: { 
              $map: { 
                  input: "$games", as: "g",
                  in: { 
                      $mergeObjects: [ 
                          "$$g", 
                          { data: { 
                               $map: { 
                                   input: "$$g.data", as: "d",
                                   in: { 
                                       $cond: [
                                           { $or: [ 
                                               { $and: [ { $eq: [ "$_id", 1 ] }, { $eq: [ "$$g.name", "Game1" ] }, { $eq: [ "$$d.data_id", 1 ] } ] },
                                               { $and: [ { $eq: [ "$_id", 2 ] }, { $eq: [ "$$g.name", "Game2" ] }, { $eq: [ "$$d.data_id", 1 ] } ] }
                                            ] }, 
                                            { $mergeObjects: [ "$$d", { valid: true } ] }, 
                                            "$$d"
                                       ]
                                   }
                               }
                          } }
                      ]
                  }
              }
          }
      }
  }
] ).forEach( doc => db.games.updateOne( { _id: doc._id }, { $set: { games: doc.games } } ) )