Postgres使用jsonb_array_elements查询jsonb字段时where条件匹配问题

核心本质：数据类型不匹配 #

你遇到的问题本质是PostgreSQL中jsonb类型和原生text类型的运算逻辑差异，具体可以拆解为3点：

• 首先，jsonb类型的取值运算符->返回的结果始终是jsonb类型，而非原生字符串：你SQL中jsonb_array_elements(role)返回的role字段是jsonb类型的字符串标量，你看到输出带双引号是客户端展示jsonb字符串的标准格式，不是值本身额外携带了双引号。
• 其次，直接用=比较时类型隐式转换不符合预期：你写的等值条件中，左边是jsonb类型，右边是text类型，PostgreSQL做跨类型等值比较时不会自动将右边的text转换为等价的jsonb字符串，最终等值判断返回假。
• 最后，like是原生text类型的运算符：当你对jsonb类型使用like时，PostgreSQL会自动将jsonb值转换为它的字符串显示形式（也就是带双引号的格式），因此'%"role1"%'可以成功匹配。

你可以根据场景选择以下任意一种方案解决匹配问题：

1. 提取值时直接取原生text类型：将jsonb字符串转成原生字符串再比较，不需要额外处理引号

select jsonb_array_elements(role) #>> '{}' as role 
from (
    select x -> 'roles' as role
    from test,
         jsonb_array_elements(data->'auth') x
) t
where role = 'role1'

2. 等值比较时将右边的值转成jsonb类型：

where role = '"role1"'::jsonb

3. 无需拆数组直接用jsonb包含运算符查询，性能更高且支持GIN索引加速：

select * from test 
where data @> '{"auth": [{"roles": ["role1"]}]}'::jsonb

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