如何筛选移除JSON列含Kiwi的行？解决JSON函数未找到报错

核心需求 #

排除所有JSON数组内任意对象的name字段为Kiwi的行，仅保留完全未出现Kiwi的行。

分数据库类型解决： #

1. MySQL/MariaDB 环境 #

如果使用的是MySQL 5.7+ 或 MariaDB，你之前的JSON_CONTAINS用法有误，需指定匹配对象的name字段，正确写法如下：

SELECT * FROM persons
WHERE NOT JSON_CONTAINS(fruits, '{"name": "Kiwi"}', '$');

也可以用JSON_SEARCH检查是否存在匹配的name值，无匹配则保留该行：

SELECT * FROM persons
WHERE JSON_SEARCH(fruits, 'one', 'Kiwi', NULL, '$[*].name') IS NULL;

• $[*].name用于遍历数组中每个对象的name字段，'one'表示找到第一个匹配就停止，效率更高。

若你的MySQL版本低于5.7（不支持JSON原生函数），或JSON列用TEXT类型存储，可退而求其次用字符串匹配（注意：可能存在误判，比如其他字段恰好有相同字符串）：

SELECT * FROM persons
WHERE fruits NOT LIKE '%"name":"Kiwi"%';

2. PostgreSQL 环境 #

PostgreSQL的JSON函数逻辑与MySQL不同，推荐两种高效写法：

方法一：展开数组过滤

SELECT DISTINCT p.* FROM persons p
WHERE NOT EXISTS (
    SELECT 1 FROM jsonb_array_elements(p.fruits::jsonb) AS elem
    WHERE elem->>'name' = 'Kiwi'
);

方法二：用@>操作符（性能更优）

SELECT * FROM persons
WHERE NOT (fruits::jsonb @> '[{"name": "Kiwi"}]');

• 建议将JSON列转为jsonb类型，PostgreSQL对该类型的查询优化更好；若列本身是json类型，去掉::jsonb即可使用。

之前写法报错的原因 #

• JSON_CONTAINS是MySQL 5.7才新增的函数，版本过低会提示“函数未找到”；
• JSON_ARRAY_CONTAINS、JSON_EXTRACT_ARRAY并非MySQL原生函数，你可能混淆了其他数据库（如Hive）的语法；
• 即便函数可用，你之前未指定匹配name字段，而是直接匹配整个JSON结构，无法定位到目标值。

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