PostgreSQL 13.9中JSONB数组与输入数组存在性校验报错问题

问题场景 #

使用PostgreSQL 13.9时，需要校验JSONB列中的数组与输入数组是否存在元素重叠（存在则返回true），尝试以下SQL时出现语法错误：

select 
  '  {
  "id1": ["5"],  "id2": ["5"],
  "id3": ["cc","dd" ],
  "id4": "15",   "id5": "11",
  "id6": "2",  "id7": ["12","18"]
}'::jsonb @? '$ ? ((@.id2[*] ?| ["5","7"]))'::jsonpath

错误信息：

ERROR: syntax error at or near "|" of jsonpath
input LINE 7: }'::jsonb @? '$ ? ((@.id2[*] ?| ["5","7"]))'::jsonpath
^
SQL state: 42601
Character: 153

但单个元素比较的语句可正常运行并返回true：

select 
  '  {
  "id1": ["9"],  "id2": ["5"],
  "id3": ["cc","dd" ],
  "id4": "15",   "id5": "11",
  "id6": "2",  "id7": ["12","18"]
}'::jsonb @? '$ ? ((@.id1 == "9") && (@.id2[*] == "5"))'::jsonpath 

希望采用高效方式实现，避免多个OR语句。

错误原因 #

PostgreSQL 13的jsonpath语法中，?|操作符的作用是匹配多个JSON路径中的任意一个，而非检查数组元素是否存在于指定列表中。你误用了该操作符的应用场景，导致语法错误。

正确实现方式 #

要实现数组元素与输入数组的重叠校验，需使用exists()函数结合jsonpath过滤器，在过滤器内部用@ in [元素列表]判断数组元素是否在目标列表中，exists()会验证数组中是否存在至少一个满足条件的元素。

修正后的SQL：

select 
  '  {
  "id1": ["5"],  "id2": ["5"],
  "id3": ["cc","dd" ],
  "id4": "15",   "id5": "11",
  "id6": "2",  "id7": ["12","18"]
}'::jsonb @? '$ ? (exists(@.id2[*] ? (@ in ["5","7"])))'::jsonpath

如果是针对表中JSONB列的查询，写法如下：

select * 
from your_table 
where jsonb_column @? '$ ? (exists(@.id2[*] ? (@ in ["5","7"])))';

这种写法无需拼接多个OR条件，效率与原生jsonpath优化一致。

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