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

你写的查询语法不符合PostgreSQL规范，数据库中没有INTERSECTS WITH这类操作符。针对你的需求——筛选包含键"10090"且对应数组元素存在于指定ID列表的条目，以下是几种可行的查询方案：

方法一：EXISTS子句展开数组匹配 #

可读性强，适合复杂匹配场景：

SELECT uuid, data ->> '10090'
FROM "ortholog"
WHERE 
  data ? '10090'  -- 先过滤出包含"10090"键的记录，提升查询效率
  AND EXISTS (
    SELECT 1
    FROM jsonb_array_elements(data -> '10090') AS elem
    WHERE elem::text IN ('ENSMUSG00000022347', 'ENSMUSG00000022348', 'ENSMUSG0000009422')
  );

• data ? '10090'：检查JSONB列是否存在目标键，提前排除无关记录
• jsonb_array_elements(data -> '10090')：将"10090"对应的JSONB数组展开为行级元素
• 子查询通过IN判断展开后的元素是否在指定ID列表中

方法二：数组交集操作符 #

借助PostgreSQL原生数组的交集特性实现匹配：

SELECT uuid, data ->> '10090'
FROM "ortholog"
WHERE 
  data ? '10090'
  AND ARRAY(SELECT jsonb_array_elements_text(data -> '10090')) && ARRAY['ENSMUSG00000022347', 'ENSMUSG00000022348', 'ENSMUSG0000009422'];

• jsonb_array_elements_text直接提取数组元素为文本类型，避免额外类型转换
• &&操作符判断两个数组是否存在共同元素，只要有交集即返回符合条件的记录

方法三：JSONB包含操作符结合ANY #

利用JSONB原生的包含特性批量检查：

SELECT uuid, data ->> '10090'
FROM "ortholog"
WHERE 
  data ? '10090'
  AND (data -> '10090') @> ANY(ARRAY['"ENSMUSG00000022347"', '"ENSMUSG00000022348"', '"ENSMUSG0000009422"']::jsonb[]);

• 需将目标ID转为带双引号的JSONB字符串（匹配JSONB数组的元素格式）
• @> ANY表示JSONB数组只要包含列表中的任意一个元素即符合条件

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