PostgreSQL中如何从嵌套JSONB数组查询指定元素？

问题分析 #

你的dealers表中branches是JSONB数组类型，每个数组元素是包含importeurs数组的对象。原查询语句的问题在于：

• 直接对branches数组使用->'importeurs'会返回null，因为数组本身没有importeurs键，必须先遍历数组内的元素。
• 后续出现的ERROR: operator does not exist: jsonb @? unknown是因为@?操作符要求右侧是jsonpath类型，而非普通字符串。

正确查询方案 #

方案1：使用@>操作符匹配完整结构 #

利用JSONB的包含操作符@>，构造与branches数组元素结构一致的JSON，直接匹配：

SELECT * FROM dealers
WHERE branches @> '[{"importeurs": [{"akz": "BAB", "number": "767676"}]}]';

该语句逻辑：检查branches数组中是否存在至少一个元素，其importeurs数组包含目标对象。

方案2：展开数组后匹配 #

通过jsonb_array_elements展开branches数组，再对每个元素的importeurs进行匹配：

SELECT DISTINCT d.* FROM dealers d
CROSS JOIN LATERAL jsonb_array_elements(d.branches) AS branch
WHERE branch->'importeurs' @> '[{"akz": "BAB", "number": "767676"}]';

使用DISTINCT是为了避免同一记录因branches中有多个符合条件的元素而被重复返回。

方案3：使用JSONPath查询 #

用jsonb_path_exists函数通过JSONPath表达式精准匹配嵌套数组中的元素：

SELECT * FROM dealers
WHERE jsonb_path_exists(branches, '$.[]?(@.importeurs[]?(@.akz == "BAB" && @.number == "767676"))');

JSONPath表达式解释：

• $.[]：遍历branches数组的所有元素
• ?(@.importeurs[]?(@.akz == "BAB" && @.number == "767676"))：筛选出importeurs数组中存在满足akz和number条件元素的分支

内容的提问来源于stack exchange，提问作者J.Z.