MySQL 8.0中如何使用JSON_REMOVE移除数组内多个含指定键值对的字典？

我来帮你梳理下为什么原来的更新语句没生效，以及如何正确实现需求：

问题根源分析 #

你原来的UPDATE语句失效主要有两个核心原因：

1. 路径指向错误：JSON_SEARCH返回的是匹配a字段的路径（比如["$[0].a", "$[1].a"]），但我们需要移除的是整个数组元素，而非元素里的a字段，正确的路径应该是$[0]、$[1]这种直接指向数组元素的格式。
2. JSON_REMOVE不支持数组参数：JSON_REMOVE需要逐个传入路径参数，而且如果直接按原索引顺序移除，先删除$[0]后，原来的$[1]会变成新的$[0]，导致后续路径直接失效。

正确解决方案：用JSON_TABLE展开过滤再聚合 #

MySQL 8.0提供的JSON_TABLE函数可以把JSON数组转换成关系型表结构，我们可以先展开数组、过滤掉不需要的元素，再重新聚合成JSON数组，这是最稳妥的处理方式。

步骤1（可选）：给表添加主键（如果没有的话） #

为了确保每行数据能被独立处理，建议给表添加一个自增主键：

ALTER TABLE test ADD COLUMN id INT AUTO_INCREMENT PRIMARY KEY;

步骤2：执行更新语句 #

UPDATE test t
SET t.`test` = IFNULL(
    (
        SELECT JSON_ARRAYAGG(j.obj)
        FROM JSON_TABLE(
            t.`test`,
            '$[*]' COLUMNS (obj JSON PATH '$')
        ) j
        WHERE JSON_UNQUOTE(JSON_EXTRACT(j.obj, '$.a')) != '1'
    ),
    JSON_ARRAY()
);

代码逻辑解释 #

• JSON_TABLE：把t.test中的JSON数组展开成临时表j，每行存储一个数组元素（obj列）。
• WHERE子句：过滤掉所有a字段等于'1'的JSON对象。
• JSON_ARRAYAGG：将过滤后的元素重新聚合成一个完整的JSON数组。
• IFNULL：处理原数组为空的情况，确保更新后依然保留[]而非NULL。

验证结果 #

执行你预期的查询语句：

SELECT JSON_UNQUOTE(JSON_SEARCH(`test`, 'all', '1', null, '$[*].a')) `data`, `test` FROM `test`;

会得到完全符合预期的结果：

+------+------------------------------------------------------------------------------------------------------------------+
| data | test                                                                                                              |
+------+------------------------------------------------------------------------------------------------------------------+
| NULL | [{"a": "2", "b": "3"}, {"a": "2", "b": "-3"}, {"a": "3", "b": "4"}]                                               |
| NULL | []                                                                                                                |
| NULL | []                                                                                                                |
+------+------------------------------------------------------------------------------------------------------------------+

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