PostgreSQL：如何在jsonb列中按嵌套JSON的code键值查询记录

你遇到的错误是因为jsonb_array_elements是返回多行结果的集合函数（set-returning function），WHERE子句需要的是单个布尔值，直接在这里使用这类函数会违反语法规则。下面给你几种可行的解决方案：

方法1：使用EXISTS子查询（兼容性最好） #

把集合函数放到EXISTS子查询中，这样就能检查数组中是否存在符合条件的元素，语法兼容所有支持jsonb的PostgreSQL版本（9.4+）：

SELECT * FROM sometable t
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(t.data->'data') AS elem
    WHERE elem->'type'->>'code' = 'B'
);

解释： #

• jsonb_array_elements(t.data->'data')把data字段里的JSON数组拆分成单独的jsonb元素
• elem->'type'->>'code'依次获取每个元素的type对象，再提取code的文本值
• 只要子查询能找到匹配'B'的元素，主查询就会返回这条记录

方法2：使用jsonb_path_exists（PostgreSQL 12+） #

如果你用的是PostgreSQL 12或更高版本，可以用更简洁的JSON路径查询：

SELECT * FROM sometable t
WHERE jsonb_path_exists(t.data, '$.data[*].type.code ? (@ == "B")');

解释： #

• $.data[*]遍历data数组中的所有元素
• .type.code定位到每个元素下的type.code值
• ? (@ == "B")筛选出值等于"B"的元素，只要存在这样的元素，条件就成立

方法3：使用jsonb包含操作符（简洁写法） #

如果你的JSON结构固定，也可以构造一个目标JSON片段，用@>操作符检查是否包含：

SELECT * FROM sometable t
WHERE t.data->'data' @> '[{"type": {"code": "B"}}]';

解释： #

• @>是jsonb的包含操作符，检查左边的JSON数组是否包含右边指定的JSON元素（只要数组中有至少一个元素满足子集匹配即可）
• 这种写法非常简洁，适合结构固定、只需要匹配特定键值的场景

你可以根据自己的PostgreSQL版本和需求选择合适的方法，一般推荐EXISTS子查询的方法，兼容性最好，逻辑也清晰。

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