PostgreSQL 10.7中jsonb嵌套数组对象的属性过滤查询求助

你的问题出在对嵌套数组的匹配逻辑上：attrs#>'Info,Groups,Users'返回的是一个数组的数组（每个Group对应一个Users数组），而@>运算符只能检查顶层数组是否包含指定元素，无法穿透嵌套结构匹配内层数组的元素。

以下是几种可行的解决方案：

方法1：使用jsonb_path_exists（推荐，简洁直观） #

PostgreSQL 10及以上支持JSONPath查询，可以直接遍历嵌套数组并筛选：

WHERE jsonb_path_exists(attrs, '$.Info.Groups[*].Users[*] ? (@.FirstName == "John")')

• 逻辑说明：$.Info.Groups[*]遍历所有分组，.Users[*]遍历每个分组下的所有用户，? (@.FirstName == "John")匹配FirstName为John的用户，只要存在符合条件的记录就返回true。

方法2：使用数组展开（适合需要处理展开后数据的场景） #

通过jsonb_array_elements将嵌套数组展开为行记录，再进行筛选：

SELECT DISTINCT t.*
FROM your_table t
-- 展开Groups数组
JOIN jsonb_array_elements(t.attrs->'Info'->'Groups') AS grp
-- 展开每个Group下的Users数组，并筛选目标用户
JOIN jsonb_array_elements(grp->'Users') AS usr ON (usr->>'FirstName') = 'John'

• 逻辑说明：先把Groups数组拆分成单独的分组行，再拆分每个分组的Users数组，找到FirstName为John的用户后，关联回原表并去重，得到包含目标用户的分组所在的原表记录。

方法3：修正@>运算符的匹配结构（适合有GIN索引优化的场景） #

如果你的attrs列有GIN索引，这种方式可以利用索引提升性能，核心是匹配Groups数组中存在一个包含目标Users的分组：

WHERE attrs @> '{"Info": {"Groups": [{"Users": [{"FirstName": "John"}]}]}}'::jsonb

或者用路径运算符简化写法：

WHERE attrs#>'{Info,Groups}' @> '[{"Users": [{"FirstName": "John"}]}]'::jsonb

• 逻辑说明：@>运算符检查attrs#>'{Info,Groups}'返回的Groups数组中，是否存在至少一个分组对象，其Users数组包含{"FirstName": "John"}这个用户对象。

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