MySQL 8.0中JSON数组元素移除问题：json_remove与json_search失效

问题场景 #

在MySQL 8.0中尝试用json_remove和json_search批量移除JSON数组中的指定元素时，无法达到预期效果。以books表为例，public_unit_ids字段值为'[5630, 5631]'，执行以下查询语句：

select  
replace(json_search(REGEXP_REPLACE(public_unit_ids, '(5630|5631)', '""'), 'all', ''), '"', '') as new_js_replace,
json_remove(public_unit_ids, replace(json_search(REGEXP_REPLACE(public_unit_ids, '(5630|5631)', '""'), 'one', ''), '"', '')) as new_js_remove from books

实际执行结果 #

预期结果 #

问题原因 #

1. json_search使用'one'参数时，只会返回第一个匹配元素的路径，导致json_remove仅能移除一个元素；
2. 原语句中用REGEXP_REPLACE将数值替换为空字符串的操作完全多余，反而增加了路径解析的复杂度；
3. json_search返回的路径是带双引号的字符串，直接用replace转义不如用JSON_UNQUOTE更可靠，容易出现格式问题。

解决方案 #

方案1：直接传入多路径参数（适用于固定要移除的元素）

利用json_remove支持多个路径参数的特性，直接获取每个目标元素的路径并传入：

SELECT
  JSON_SEARCH(public_unit_ids, 'all', 5630) AS path_5630,
  JSON_SEARCH(public_unit_ids, 'all', 5631) AS path_5631,
  JSON_REMOVE(
    public_unit_ids,
    JSON_UNQUOTE(JSON_SEARCH(public_unit_ids, 'one', 5630)),
    JSON_UNQUOTE(JSON_SEARCH(public_unit_ids, 'one', 5631))
  ) AS new_js_remove
FROM books;

方案2：动态批量处理（适用于不确定要移除的元素数量）

用JSON_TABLE将json_search返回的多路径拆分为行，再聚合为逗号分隔的参数传入json_remove：

WITH path_list AS (
  SELECT
    b.id,
    JSON_UNQUOTE(jt.path) AS element_path
  FROM books b,
       JSON_TABLE(
         JSON_SEARCH(b.public_unit_ids, 'all', '', NULL, '$[*]'),
         '$[*]' COLUMNS path VARCHAR(50) PATH '$'
       ) jt
  WHERE JSON_EXTRACT(b.public_unit_ids, jt.path) IN (5630, 5631)
)
SELECT
  b.id,
  JSON_REMOVE(b.public_unit_ids, GROUP_CONCAT(p.element_path SEPARATOR ', ')) AS new_js_remove
FROM books b
JOIN path_list p ON b.id = p.id
GROUP BY b.id;

方案3：更简洁的过滤法（推荐）

跳过json_search和json_remove的组合，直接用JSON_ARRAYAGG过滤掉不需要的元素：

SELECT
  JSON_ARRAYAGG(jt.value) AS new_js_remove
FROM books b,
     JSON_TABLE(b.public_unit_ids, '$[*]' COLUMNS value INT PATH '$') jt
WHERE jt.value NOT IN (5630, 5631)
GROUP BY b.id;

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