MySQL中如何删除JSON数组中不含指定子串的元素？

完全可以用SQL的JSON函数实现这个需求，下面针对主流数据库给出具体实现：

1. MySQL 实现 #

假设你的表名为target_table，存储JSON的列名为json_col，需保留的子串集合为'a'、'bc'、'def'，表有主键id用于关联。

具体SQL #

UPDATE target_table t
JOIN (
    SELECT 
        id,
        JSON_OBJECT(
            'name', JSON_UNQUOTE(JSON_EXTRACT(t.json_col, '$.name')),
            'list', JSON_ARRAYAGG(j.element)
        ) AS filtered_json
    FROM target_table t
    JOIN JSON_TABLE(
        t.json_col,
        '$.list[*]' COLUMNS (element VARCHAR(255) PATH '$')
    ) j
    -- 匹配任一目标子串
    WHERE j.element LIKE '%a%' 
       OR j.element LIKE '%bc%' 
       OR j.element LIKE '%def%'
    GROUP BY id, JSON_UNQUOTE(JSON_EXTRACT(t.json_col, '$.name'))
) filtered ON t.id = filtered.id
SET t.json_col = filtered.filtered_json;

关键逻辑说明 #

• JSON_TABLE：将JSON数组拆分为单行记录（MySQL 8.0+支持）
• JSON_ARRAYAGG：把筛选后的元素重新聚合成JSON数组
• JSON_OBJECT：重组原JSON结构，保留name字段和过滤后的list数组

2. PostgreSQL 实现 #

假设表名为target_table，JSON列类型为jsonb（推荐使用，性能更优），主键为id。

具体SQL #

UPDATE target_table t
SET json_col = jsonb_set(
    t.json_col,
    '{list}',
    (
        SELECT jsonb_agg(element)
        FROM jsonb_array_elements_text(t.json_col -> 'list') AS element
        WHERE element LIKE '%a%'
           OR element LIKE '%bc%'
           OR element LIKE '%def%'
    )
)
WHERE EXISTS (
    -- 仅更新存在符合条件元素的行，避免空数组覆盖原数据
    SELECT 1
    FROM jsonb_array_elements_text(t.json_col -> 'list') AS element
    WHERE element LIKE '%a%'
       OR element LIKE '%bc%'
       OR element LIKE '%def%'
);

关键逻辑说明 #

• jsonb_array_elements_text：拆分JSON数组为文本行
• jsonb_agg：聚合筛选后的元素为jsonb数组
• jsonb_set：将原JSON的list字段替换为过滤后的数组

如果使用其他数据库（如SQL Server），可遵循「拆分行→筛选匹配元素→聚合为数组→更新原字段」的思路，利用对应数据库的JSON函数调整实现即可。

内容的提问来源于stack exchange，提问作者이준서