PostgreSQL中查询JSONB字段值存在于指定列表的行

我懂你想要的效果——找出data列里的gender数组和给定的('men', 'women', 'something_else')列表有任意重叠的行对吧？PostgreSQL确实没有INTERSECTS WITH这个关键字，但我们可以用几个内置函数轻松实现这个需求，下面给你几种实用的方案：

方案一：用&&运算符直接判断数组交集（最简洁推荐） #

PostgreSQL的JSONB类型支持&&运算符，专门用来判断两个数组是否存在交集，写法超级直观：

SELECT uuid, data ->> 'gender' 
FROM "OX_Articles"
WHERE (data -> 'gender') && '{men, women, something_else}'::jsonb;

解释下：

• data -> 'gender'取出data列里的genderJSONB数组
• '{men, women, something_else}'::jsonb把你的查询列表转换成JSONB数组
• &&会直接返回两个数组是否有共同元素，满足条件的行就会被筛选出来

方案二：用jsonb_exists_any函数（PostgreSQL 12+适用） #

如果你的PostgreSQL版本是12或更高，还可以用官方提供的jsonb_exists_any函数，语义更明确：

SELECT uuid, data ->> 'gender' 
FROM "OX_Articles"
WHERE jsonb_exists_any(data -> 'gender', '{men, women, something_else}'::text[]);

这个函数就是专门用来检查JSONB数组是否包含指定文本列表中的任意元素，代码可读性拉满。

方案三：用jsonb_array_elements_text配合EXISTS（兼容旧版本） #

要是你用的是PostgreSQL 12之前的版本，就可以用数组展开+子查询的方式实现：

SELECT uuid, data ->> 'gender' 
FROM "OX_Articles"
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements_text(data -> 'gender') AS gender_item
    WHERE gender_item = ANY('{men, women, something_else}'::text[])
);

逻辑是把gender数组拆成单独的文本行，只要其中某一行匹配查询列表里的任意元素，就保留这条记录。

内容的提问来源于stack exchange，提问作者Alex G.P.