MySQL中批量替换JSON文档内指定键值对的实现方法咨询

问题分析 #

你之前尝试的JSON_MERGE_PATCH和JSON_REPLACE不生效的核心原因是：

• MySQL的JSON路径表达式不支持在JSON_REPLACE/JSON_MERGE_PATCH里直接用通配符*批量更新数组内所有对象的指定字段，通配符*仅支持在JSON_SEARCH、JSON_EXTRACT这类查询类函数中使用
• 你写的自定义函数存在两处明显错误：
  1. 取数组长度的语句错误，应该取eventbyminutes节点的长度而非整个JSON的长度：json_length(injsondata, '$.eventbyminutes')
  2. 路径字符串里的i是变量不能直接写在字符串里，需要用CONCAT拼接生成动态路径

可行实现方案 #

方案1：MySQL 8.0+ 单SELECT语句实现（无需自定义函数） #

利用JSON_TABLE拆解数组，修改字段后再用JSON_ARRAYAGG拼接回JSON，全程不需要自定义函数：

SELECT 
  JSON_SET(
    @injsondata,
    '$.eventbyminutes',
    JSON_ARRAYAGG(
      JSON_SET(item, '$.matchid', 10002)
    )
  ) AS modified_json
FROM JSON_TABLE(
  @injsondata,
  '$.eventbyminutes[*]' COLUMNS (
    item JSON PATH '$'
  )
) AS items;

这个语句的逻辑是：

1. 用JSON_TABLE把eventbyminutes数组里的每个元素拆成单独的行
2. 对每一行的元素用JSON_SET修改matchid为10002
3. 用JSON_ARRAYAGG把修改后的所有元素重新拼接为数组
4. 用最外层的JSON_SET把原JSON里的eventbyminutes替换为修改后的数组

方案2：修正后的自定义函数实现 #

如果需要兼容更低版本的MySQL，你原来的思路是可行的，修正错误后的函数如下：

DELIMITER $$
CREATE DEFINER=`root`@`localhost` FUNCTION `func_modify_json`(injsondata JSON) RETURNS JSON
DETERMINISTIC
BEGIN
    DECLARE len INT DEFAULT JSON_LENGTH(injsondata, '$.eventbyminutes');
    DECLARE i INT DEFAULT 0;
    DECLARE outjsondata JSON DEFAULT injsondata;

    WHILE i < len DO
        SET outjsondata = JSON_REPLACE(
            outjsondata,
            CONCAT('$.eventbyminutes[', i, '].matchid'),
            10002
        );
        SET i = i + 1;
    END WHILE;

    RETURN outjsondata;
END$$
DELIMITER ;

调用方式：

SELECT func_modify_json(@injsondata) AS modified_json;

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