MongoDB匹配_id及数组多条件更新数组元素的异常问题排查

原始集合数据 #

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

需求说明 #

匹配以下条件更新issues.freq字段：

• _id: ObjectId("64390a8796f544082a67146a")
• issues.vol: "2" 且 issues.num: "1"
  将对应元素的freq从11修改为45。

当前错误的查询语句 #

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

执行后的错误结果 #

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

错误原因 #

你的查询条件是分开匹配数组元素的："issues.vol": "2" 会匹配数组中任意vol为2的元素，"issues.num": "1" 会匹配数组中任意num为1的元素。只要文档同时满足这两个独立条件，就会被选中。而$占位符默认只会匹配第一个满足查询条件的数组元素——这里第一个vol为2的元素是{"vol": "2", "num": "2", "freq": 9}，所以这个元素的freq被错误更新了，而非同时满足vol=2和num=1的目标元素。

正确的解决方法 #

需要用$elemMatch来指定数组元素必须同时满足多个条件，这样查询条件和$占位符就能精准对应到目标元素：

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

执行后的正确结果 #

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

内容的提问来源于stack exchange，提问作者sariDon