在MySQL中搜索替换JSON值、修改JSON数组的最优雅实现方法是什么？

前提要求 #

MySQL 8.0 及以上版本，内置的JSON函数可直接实现需求，无需拆表或额外业务代码处理。

核心思路 #

无需关注数组内对象的顺序，通过JSON路径匹配或者JSON_TABLE拆列的方式定位到指定class的对象，直接完成字段的增删改操作。

方案1：JSON路径直接匹配（适合简单修改场景） #

直接通过JSON路径条件表达式定位目标对象，配合JSON_SET、JSON_REMOVE完成操作，单条UPDATE语句即可实现。

以你举例的需求为例：定位class为com.parallelorigin.code.ecs.components.Identity的对象，替换class值、新增name字段、修改tag值、删除typeID字段，实现代码如下：

UPDATE Entity
SET Json = JSON_REMOVE(
            JSON_SET(
              Json,
              -- 替换class字段值
              '$[*]?(@.class == "com.parallelorigin.code.ecs.components.Identity").class',
              "com.newpath.ecs.components.Identity",
              -- 新增name字段
              '$[*]?(@.class == "com.parallelorigin.code.ecs.components.Identity").name',
              "玩家001",
              -- 修改tag字段值
              '$[*]?(@.class == "com.parallelorigin.code.ecs.components.Identity").tag',
              "main_player"
            ),
            -- 删除typeID字段
            '$[*]?(@.class == "com.parallelorigin.code.ecs.components.Identity").typeID'
          )
-- 过滤仅更新包含目标class的行，提升性能
WHERE JSON_CONTAINS(Json, '{"class":"com.parallelorigin.code.ecs.components.Identity"}', '$[*]');

注：$[*]?(@.class == "xxx")是MySQL 8.0.17及以上支持的JSON路径条件表达式，写法简洁直观，完全不需要关心数组下标。

方案2：JSON_TABLE拆合处理（适合复杂修改/批量操作场景） #

如果需要修改的逻辑比较复杂，或者要批量替换多个class的路径，用JSON_TABLE把JSON数组拆成单行对象处理后再聚合，可读性和可维护性更高：

UPDATE Entity e
INNER JOIN (
  SELECT 
    ID,
    JSON_ARRAYAGG(
      CASE 
        -- 匹配目标对象时执行修改
        WHEN j.item->>'$.class' = 'com.parallelorigin.code.ecs.components.Identity' 
        THEN JSON_REMOVE(
               JSON_SET(j.item, 
                 '$.class', 'com.newpath.ecs.components.Identity',
                 '$.name', '玩家001',
                 '$.tag', 'main_player'
               ), 
               '$.typeID'
             )
        -- 非目标对象原样保留
        ELSE j.item 
      END
    ) as new_Json
  FROM Entity,
       JSON_TABLE(Json, '$[*]' COLUMNS (item JSON PATH '$')) j
  -- 仅处理包含目标对象的行
  WHERE JSON_CONTAINS(Json, '{"class":"com.parallelorigin.code.ecs.components.Identity"}', '$[*]')
  GROUP BY ID
) t ON e.ID = t.ID
SET e.Json = t.new_Json;

这个方案完全不依赖数组内对象的顺序，处理完成后数组原有对象的顺序也不会改变，适合生产环境批量数据更新。

方案选择建议 #

• 单次修改少量字段、MySQL版本足够新优先选方案1，代码简洁执行效率高
• 修改逻辑复杂、需要批量处理多个class对象优先选方案2，逻辑清晰不易出错

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