MySQL 5.7中转换JSON数组结构并移除指定字段的方法

问题解答 #

JSON_SEARCH作为参数的可行性

JSON_SEARCH返回的是匹配指定值的JSON路径字符串，理论上可以作为JSON_REMOVE的参数，但它的两个核心限制导致无法适配你的场景：

1. 即便使用'all'参数返回所有匹配路径，结果也是路径组成的JSON数组，而JSON_REMOVE仅接受独立的路径参数，不能直接传入路径数组；
2. JSON_SEARCH是基于值的搜索，无法直接定位$.foo[*].qux这类带通配符的字段路径，没法批量匹配所有数组元素中的qux字段。

因此，JSON_SEARCH + JSON_REMOVE的组合无法实现你需要的批量移除操作。

MySQL 5.7下的可行方案

由于MySQL 5.7不支持8.0引入的JSON_TABLE，我们可以通过生成索引序列、提取目标字段、重新组装JSON的方式实现需求：

假设你的表名为your_table，JSON字段为json_col，执行以下SQL：

SELECT 
  JSON_OBJECT(
    'foo', JSON_ARRAYAGG(
      JSON_ARRAY(
        JSON_UNQUOTE(JSON_EXTRACT(json_col, CONCAT('$.foo[', idx, '].bar'))),
        JSON_UNQUOTE(JSON_EXTRACT(json_col, CONCAT('$.foo[', idx, '].baz')))
      )
    )
  ) AS transformed_json
FROM your_table,
(SELECT @row := @row + 1 AS idx FROM 
  (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) t1,
  (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) t2,
  (SELECT @row := -1) t0
) AS indexes
WHERE JSON_EXTRACT(json_col, CONCAT('$.foo[', idx, ']')) IS NOT NULL
GROUP BY json_col;

代码说明

• 子查询indexes生成0到99的索引序列（可通过增加UNION ALL数量扩大支持的数组元素上限），遍历JSON数组的每个元素；
• JSON_EXTRACT结合动态拼接的路径，提取每个元素的bar和baz值，JSON_UNQUOTE去除字符串引号；
• JSON_ARRAY将两个字段值组装成子数组，JSON_ARRAYAGG将所有子数组合并为大数组；
• JSON_OBJECT最终封装成你需要的JSON结构。

注意事项

• 若JSON数组元素数量超过100，需调整子查询中UNION ALL的数量来扩展索引序列范围；
• GROUP BY json_col确保表中每条记录的JSON被独立处理。

内容的提问来源于stack exchange，提问作者Yves M.