PostgreSQL如何查询深层嵌套jsonb数组中指定键值的记录

你的原查询无法返回预期结果，核心是字段的多层嵌套结构里，qos1、country_id均为数组类型，而非普通键值对象。常规的->链式操作仅支持提取对象的指定key、或数组的指定下标元素，不会自动遍历数组所有元素做匹配，直接链式写->'qos1'->'country_id'实际取到的是null，自然无法匹配到目标值。

方案1：JSON路径匹配（推荐，PostgreSQL 12+版本支持） #

用@?操作符配合jsonpath语法，直接声明匹配路径即可，写法最简洁，性能也更好：

SELECT *
FROM mytable
WHERE datacolumn @? '$[*].qos1[*].country_id[*].id ? (@ == "FR")';

语法说明：路径里的[*]代表遍历当前层级数组的所有元素，不需要写死数组下标，末尾的判断规则就是匹配id值等于"FR"的节点。

方案2：数组逐层展开（兼容所有PostgreSQL版本） #

如果你的数据库版本低于12不支持jsonpath，可以用jsonb_array_elements函数逐层拆分数组，再做值匹配：

SELECT DISTINCT t.*
FROM mytable t
-- 拆分最外层数组
CROSS JOIN jsonb_array_elements(t.datacolumn) top_level
-- 拆分qos1层数组
CROSS JOIN jsonb_array_elements(top_level->'qos1') qos1_item
-- 拆分country_id层数组
CROSS JOIN jsonb_array_elements(qos1_item->'country_id') country_item
WHERE country_item->>'id' = 'FR';

注意这里要加DISTINCT去重，避免同一行因为存在多个匹配节点被重复返回；用->>操作符可以直接取出节点的文本值，不需要像原写法那样手动给匹配值加双引号。

• 未处理数组层级的遍历逻辑，对数组类型直接用key取值，返回结果恒为null
• 取值逻辑错误的前提下，后续的值比对完全不会生效

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