PostgreSQL中如何基于JSONB数组包含关系关联两张表？

你的SQL无法运行是因为@>运算符的类型要求不满足：该运算符两边必须都是JSONB类型，且判断数组包含单个元素时，右侧得是JSONB格式的单个值，不能直接传入字符串类型的baz.val。以下是几种可行的正确写法：

方法1：转换字符串为JSONB后使用@>运算符 #

select *
from baz
join foo
on foo.vals @> to_jsonb(baz.val);

to_jsonb(baz.val)会把baz.val的字符串值转为JSONB类型，让数组的包含判断逻辑正常生效。

方法2：展开JSONB数组后匹配 #

select *
from baz
join foo on baz.val = any(jsonb_array_elements_text(foo.vals));

jsonb_array_elements_text(foo.vals)会把JSONB数组展开为独立的字符串元素，再判断baz.val是否等于其中某一个元素。

方法3：使用jsonb_exists函数（PostgreSQL 12+） #

select *
from baz
join foo
on jsonb_exists(foo.vals, baz.val);

这个函数可以直接检查JSONB数组中是否存在指定的字符串元素，写法更简洁直观。

性能优化提示 #

如果表数据量较大或者数组元素较多，建议给foo.vals创建GIN索引来提升关联查询的效率：

CREATE INDEX idx_foo_vals ON foo USING GIN (vals);

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