如何用PSQL查询包含指定字符串的嵌套数组jsonb列

原查询的问题 #

你的查询语句存在两个局限性：

• 仅检查bar数组的第一个元素（->0），如果目标对象在bar的其他索引位置，会直接漏掉
• 是精确匹配整个baz数组等于["special string"]，但需求是baz数组包含该字符串（哪怕数组还有其他元素也应匹配）

正确的查询方案 #

方案1：使用jsonb_path_exists（推荐，灵活支持复杂路径）

通过JSONPath语法遍历所有嵌套元素，检查是否存在符合条件的项：

SELECT *
FROM your_table
WHERE jsonb_path_exists(foo, '$.bar[*].baz[*] ? (@ == "special string")');

• $.bar[*]：遍历bar数组的所有元素
• baz[*]：遍历每个bar元素下baz数组的所有元素
• ? (@ == "special string")：过滤出值等于目标字符串的元素

方案2：展开数组后过滤

通过jsonb_array_elements将bar数组展开为多行，再检查每个对象的baz数组：

SELECT DISTINCT t.*
FROM your_table t
CROSS JOIN jsonb_array_elements(t.foo->'bar') AS bar_obj
WHERE bar_obj->'baz' @> '"special string"'::jsonb;

• jsonb_array_elements(t.foo->'bar')：把bar数组拆分成独立的行记录
• @>：JSONB的包含操作符，检查baz数组是否包含目标字符串（需将字符串转为JSONB类型）
• DISTINCT：避免同一个原表记录被多次返回（如果bar数组有多个符合条件的对象）

方案3：使用@>直接匹配嵌套结构

如果只需检查bar中存在至少一个对象的baz数组包含目标字符串，可直接用包含操作符：

SELECT *
FROM your_table
WHERE foo @> '{"bar": [{"baz": ["special string"]}]}'::jsonb;

这个写法简洁高效，适合明确嵌套结构的场景，PostgreSQL会利用JSONB的GIN索引（如果已创建）提升查询速度。

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