MySQL 8修改JSON数组指定元素值失败，请求排查代码错误

问题背景 #

现有JSON结构如下：

{"details": {
  "product_name": "Example Product",
  "description": "This is an example product",
  "price": 10.99,
  "dimensions": {
    "width": 10,
    "height": 15,
    "depth": 5
  },
  "reviews": [
    {
      "author": "John Smith",
      "rating": 4,
      "comment": "Great product, would buy again"
    },
    {
      "author": "Jane Doe",
      "rating": 3,
      "comment": "Product was okay, not great"
    }
  ]
 }}

需求：修改reviews数组中rating等于3的元素的author字段值。

尝试的SQL语句 #

UPDATE my_table SET json_data = JSON_REPLACE(json_data, 
    JSON_UNQUOTE(
        REPLACE(
            JSON_SEARCH(json_data, 'one', 3, NULL, '$.details.reviews[*].rating'), 
            '.rating', '.author'
        )
    ),
    'New Author'
)
WHERE JSON_SEARCH(json_data, 'one', 3, NULL, '$.details.reviews[*].rating') IS NOT NULL;

错误原因 #

核心问题是JSON_SEARCH默认只匹配字符串类型的值，而你的rating是数值类型的3，JSON_SEARCH会在JSON里找字符串"3"而非数值3，所以根本找不到对应的路径，WHERE条件不成立，自然不会执行任何更新。

即使你把搜索值改成字符串"3"，因为JSON里的rating是数值类型，类型不匹配依然匹配不到。

正确解决方案 #

方法一：用JSON_CONTAINS匹配数值，构造路径更新（MySQL 5.7+支持） #

UPDATE my_table 
SET json_data = JSON_REPLACE(
    json_data,
    REPLACE(
        JSON_UNQUOTE(JSON_SEARCH(json_data, 'one', CAST(3 AS CHAR), NULL, '$.details.reviews[*].rating')),
        '.rating',
        '.author'
    ),
    'New Author'
)
WHERE JSON_CONTAINS(json_data, '3', '$.details.reviews[*].rating');

这里用JSON_CONTAINS匹配数值3（第二个参数传JSON格式的数值字符串'3'），同时用CAST(3 AS CHAR)让JSON_SEARCH能匹配到对应路径。

方法二：用JSON_TABLE拆分数组处理（MySQL 8.0+推荐） #

这种方法逻辑更清晰，适合复杂JSON数组操作：

UPDATE my_table t
JOIN (
    SELECT 
        id,
        JSON_UNQUOTE(JSON_SEARCH(t.json_data, 'one', 3, NULL, CONCAT('$.details.reviews[', idx, '].rating'))) AS rating_path
    FROM my_table t,
         JSON_TABLE(
             t.json_data,
             '$.details.reviews[*]' COLUMNS (
                 idx FOR ORDINALITY,
                 rating INT PATH '$.rating'
             )
         ) j
    WHERE j.rating = 3
) j ON t.id = j.id
SET t.json_data = JSON_REPLACE(
    t.json_data,
    REPLACE(j.rating_path, '.rating', '.author'),
    'New Author'
);

通过JSON_TABLE把reviews数组拆成关系表，找到rating=3的元素索引，再构造对应路径更新，彻底避免类型匹配问题。

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