MariaDB/MySQL中移除JSON数组指定元素及清除空对象方法

针对user_pst_tb表中pst_id=30的pst_liked_by字段（存储JSON数组[{"uid":5},{"uid":6},{"uid":7}]），要移除{"uid":6}且不遗留空对象，推荐以下两种可靠方案：

方案一：拆解数组过滤后重组（兼容所有位置，支持批量删除） #

通过JSON_TABLE将JSON数组拆分为行数据，过滤掉目标元素后，再用JSON_ARRAYAGG重新拼接成数组，完全避免空对象问题：

UPDATE user_pst_tb
SET pst_liked_by = (
    SELECT JSON_ARRAYAGG(elem)
    FROM JSON_TABLE(
        pst_liked_by,
        '$[*]' COLUMNS (elem JSON PATH '$')
    ) AS jt
    WHERE JSON_EXTRACT(elem, '$.uid') != 6
)
WHERE pst_id = 30;

如果数组中存在多个uid=6的元素，该语句会自动过滤所有匹配项，无需额外修改。

方案二：精准定位元素路径后删除（适用于单元素删除） #

先通过JSON_SEARCH找到目标元素的数组索引路径，再用JSON_REMOVE直接删除该元素：

步骤1：验证目标元素路径（可选） #

SELECT JSON_SEARCH(pst_liked_by, 'one', 6, NULL, '$[*].uid') 
FROM user_pst_tb 
WHERE pst_id = 30;

该语句会返回类似'$[1].uid'的结果，其中$[1]就是目标元素在数组中的位置。

步骤2：执行删除更新 #

UPDATE user_pst_tb
SET pst_liked_by = JSON_REMOVE(
    pst_liked_by,
    REPLACE(JSON_SEARCH(pst_liked_by, 'one', 6, NULL, '$[*].uid'), '.uid', '')
)
WHERE pst_id = 30;

若要删除所有uid=6的元素，可将JSON_SEARCH中的'one'改为'all'，但方案一处理批量删除更简洁。

问题根源说明 #

之前操作遗留空对象{}，大概率是错误使用JSON_SET将元素置为空对象而非真正移除，或是拆解数组时未正确过滤导致空行被重新聚合。上述两种方案从根源避免了这类问题。

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