如何在MariaDB中移除users表inventory字段的指定JSON元素

问题原因 #

你之前用的JSON_REMOVE(inventory, '$.[?(@.name == "money")]')会把整个字段置为NULL，是因为MariaDB的JSON_REMOVE不支持带条件过滤的JSONPath（比如$.[?()]）作为路径参数。这个函数只接受明确的数组索引路径（例如$[0]、$[1]），传入无法识别的路径时，函数会返回NULL，最终导致字段被更新为NULL。

正确解决方案 #

可以通过JSON_TABLE将JSON数组拆成行数据，过滤掉不需要的元素后，再用JSON_ARRAYAGG重新聚合为JSON数组，实现精准删除。

基础更新语句 #

UPDATE users u
SET inventory = (
    SELECT JSON_ARRAYAGG(j)
    FROM JSON_TABLE(
        u.inventory,
        '$[*]' COLUMNS (j JSON PATH '$')
    ) AS t
    WHERE JSON_VALUE(j, '$.name') != 'money'
)
WHERE 
    inventory IS NOT NULL 
    AND JSON_VALID(inventory);

处理空数组场景 #

如果原数组里只有money元素，过滤后会返回NULL，若希望此时字段值为空数组而非NULL，可添加COALESCE处理：

UPDATE users u
SET inventory = COALESCE(
    (
        SELECT JSON_ARRAYAGG(j)
        FROM JSON_TABLE(
            u.inventory,
            '$[*]' COLUMNS (j JSON PATH '$')
        ) AS t
        WHERE JSON_VALUE(j, '$.name') != 'money'
    ),
    '[]'
)
WHERE 
    inventory IS NOT NULL 
    AND JSON_VALID(inventory);

语句说明 #

1. JSON_TABLE：把inventory字段的JSON数组拆成多行，每行对应数组里的一个JSON对象（存在j列）。
2. JSON_VALUE(j, '$.name')：提取每个JSON对象的name属性值，用来判断是否需要过滤。
3. JSON_ARRAYAGG(j)：把过滤后的JSON对象重新聚合成新的JSON数组。
4. WHERE条件：只处理非空且格式有效的JSON数据，避免无效数据引发错误。

版本要求 #

JSON_TABLE函数从MariaDB 10.2.3版本开始支持，请确认你的数据库版本符合要求。

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