PostgreSQL 14+中jsonb的@@与@?操作符差异及NULL问题咨询

核心差异回顾 #

基于PostgreSQL 14文档，两个操作符的准确行为如下：

• jsonb @? jsonpath：存在性检查——判断JSONPath表达式是否能从目标JSON中匹配到任意元素，只要有匹配就返回true，否则返回false。
• jsonb @@ jsonpath：单个布尔结果获取——要求JSONPath表达式直接返回单个布尔值；如果表达式返回的是集合/数组，或者非布尔值，则返回NULL。仅当表达式是针对集合的谓词时，会隐式转为"是否存在满足条件的元素"的布尔判断。

测试用例返回NULL的原因 #

第一个测试用例 #

原SQL：

select '{"city": "Ricy", "state": "0"}'::jsonb @@ '$ ?(@.city == "Ricy" && @.state == "0")';

这里的JSONPath$ ?(...)是筛选表达式，它返回的是符合条件的顶层JSON对象（即{"city": "Ricy", "state": "0"}），而非布尔值。@@操作符无法将非布尔值转为有效结果，因此返回NULL。

修正写法：直接写布尔谓词，去掉$ ?()包裹：

select '{"city": "Ricy", "state": "0"}'::jsonb @@ '@.city == "Ricy" && @.state == "0"';

此时表达式直接计算布尔值，返回true。

第二个测试用例 #

原SQL：

select '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] ? (@ > 2)';

$.a[*] ?(@ > 2)是筛选数组中满足条件的元素，返回的是数组[3,4,5]，而非布尔值。@@拿到数组后无法解析为单个布尔结果，因此返回NULL。

而文档中的示例'{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2'返回true，是因为这个表达式是谓词表达式，PostgreSQL会隐式将其解析为"是否存在至少一个元素满足条件"的布尔判断。

修正写法有两种：

1. 直接使用谓词表达式（和文档示例一致）：

select '{"a":[1,2,3,4,5]}'::jsonb @@ '$.a[*] > 2';

2. 用exists()函数将筛选表达式转为布尔值：

select '{"a":[1,2,3,4,5]}'::jsonb @@ 'exists($.a[*] ? (@ > 2))';

两种写法都会返回true。

总结 #

• 若需检查"是否存在匹配元素"，优先使用@?操作符，它无需关注表达式返回类型，仅做存在性判断。
• 若需获取单个布尔计算结果，使用@@操作符时必须确保JSONPath表达式直接返回布尔值；针对集合筛选场景，要么用谓词形式，要么手动用exists()转为布尔值。

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