mongodb - 具有多个标识符和混合条件(AND/OR)的 arrayFilters

Question

我试图做一个匹配一些嵌套数组条件的 mongodb 查询，但没有成功......

文件:

[
   {
      "_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" }
            ]
         }
      ]
   }
]

我想做一个update_many，匹配以下数据:

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

因此，对于每个文档，我想更新一个特定的 games.data.data_id 元素。

我试过的

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}]}]

但它返回一个错误:

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

预期的输出是:

[
   {
      "_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" }
            ]
         }
      ]
   }
]

那么，我做错了什么？这该怎么做？
我尝试了其他几种选择，例如组合 $or 和 $and，但是每次我在相同条件下使用多个标识符时，它都会失败

Answer 1

根据使用规则arrayFilters ，您不能按预期使用条件，例如 _id字段内 arrayFilters .看
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.

以下是其他更新方式；但这些不能使用 arrayFilters你想要的方式。

1. 使用 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();

更多详情请访问: Bulk.find.arrayFilters

2. 使用聚合管道更新

从 MongoDB 4.2 开始， db.collection.updateMany()方法可以接受聚合管道 [ <stage1>, <stage2>, ... ]指定要执行的修改。详情请见: 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.聚合管道和更新

此聚合 + 更新适用于 4.2 之前的 MongoDB 版本。

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 } } ) )