PostgreSQL如何查询JSON列中指定键对应的数组包含特定值的记录

你遇到的报错是因为preferences为JSON/JSONB类型，不能直接用in运算符和普通字符串做匹配，需要使用对应数据库的JSON专用操作语法实现查询，不同数据库的写法如下：

PostgreSQL 数据库 #

字段类型为 JSONB（推荐存储格式，支持索引加速） #

可直接使用JSONB专用操作符实现需求：

-- 写法1：包含运算符，适合多值匹配场景，可直接加JSONB索引优化
SELECT name, preferences 
FROM accounts
WHERE preferences -> 'accepted_coupon_types' @> '["percentage"]'::jsonb;

-- 写法2：元素存在检查，适合单值匹配场景
SELECT name, preferences 
FROM accounts
WHERE preferences -> 'accepted_coupon_types' ? 'percentage';

如果需要同时匹配多个值，比如要筛选同时包含percentage和amount的记录，直接修改写法1的匹配参数即可：

SELECT name, preferences 
FROM accounts
WHERE preferences -> 'accepted_coupon_types' @> '["percentage", "amount"]'::jsonb;

字段类型为 JSON（旧存储格式，不支持索引优化） #

需要先把JSON数组转为文本数组再做匹配：

SELECT name, preferences
FROM accounts
WHERE 'percentage' IN (
    SELECT json_array_elements_text(preferences -> 'accepted_coupon_types')
);

MySQL 数据库 #

使用JSON_CONTAINS函数实现：

SELECT name, preferences
FROM accounts
WHERE JSON_CONTAINS(preferences->'$.accepted_coupon_types', '"percentage"');

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