如何查询users表inventory列含WEAPON_COMBATPISTOL的记录并删除对应JSON项

1. 查询包含WEAPON_COMBATPISTOL的记录 #

你之前的JSON_CONTAINS语法错误，原因是inventory字段存储的是JSON数组，而非单个JSON对象，无法直接通过$.name路径匹配数组内元素的属性。
推荐使用兼容性更好的JSON_SEARCH实现查询：

SELECT * FROM users 
WHERE JSON_SEARCH(inventory, 'one', 'WEAPON_COMBATPISTOL', NULL, '$[*].name') IS NOT NULL;

参数说明：

• 第一个参数为待查询的JSON字段
• one表示找到任意一个匹配项就返回，不需要遍历全数组
• 第三个参数为要匹配的属性值
• 第四个NULL为转义字符占位
• 最后一个路径参数$[*].name表示匹配数组中所有元素的name属性

2. 移除数组中name为WEAPON_COMBATPISTOL的元素 #

单匹配项场景（一个inventory数组中最多只有1个目标武器） #

UPDATE users
SET inventory = JSON_REMOVE(
    inventory,
    -- 把匹配到的name属性路径转为对应数组元素的路径
    REPLACE(JSON_UNQUOTE(JSON_SEARCH(inventory, 'one', 'WEAPON_COMBATPISTOL', NULL, '$[*].name')), '.name', '')
)
WHERE JSON_SEARCH(inventory, 'one', 'WEAPON_COMBATPISTOL', NULL, '$[*].name') IS NOT NULL;

多匹配项场景（一个inventory数组中可能存在多个目标武器） #

MySQL 8.0及以上版本可以通过JSON_TABLE过滤后重新生成数组实现全量删除：

UPDATE users u
INNER JOIN (
    SELECT 
        id, -- 此处替换为你users表的实际主键字段名
        JSON_ARRAYAGG(item) AS new_inventory
    FROM users,
         JSON_TABLE(inventory, '$[*]' COLUMNS (
             item JSON PATH '$'
         )) AS jt
    WHERE JSON_UNQUOTE(JSON_EXTRACT(item, '$.name')) != 'WEAPON_COMBATPISTOL'
    GROUP BY id
) AS filtered ON u.id = filtered.id
SET u.inventory = filtered.new_inventory
WHERE JSON_SEARCH(u.inventory, 'one', 'WEAPON_COMBATPISTOL', NULL, '$[*].name') IS NOT NULL;

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