Spring Boot中如何执行PostgreSQL JSONB操作符的原生查询？

问题背景 #

我们有一个遗留数据库的jsonb类型列，可同时存储数组和JSON数据：

items
-------
["elem1","elem2"]

["elem3","elem4"]

{"elem31": ["src1", "src2", "src3"], "elem100": ["src5"]}

{"elem21": ["src1", "src2"]}

在psql控制台中可以用?（单元素匹配）和?|（多元素任一匹配）操作符查询，但在Spring Boot原生查询中直接使用这些操作符会和参数占位符?冲突，报错java.lang.IllegalArgumentException: Mixing of ? parameters and other forms like ?1 is not supported，转义??或\?也无效。

解决方案：使用PostgreSQL内置JSONB函数替代操作符 #

PostgreSQL为这些JSONB操作符提供了对应的内置函数，完全可以替代操作符功能，同时避免与Spring的参数占位符冲突：

1. 单元素匹配（替代?操作符） #

使用jsonb_exists(jsonb_column, text)函数，对应原查询item ? 'elem21'：

@Query("select * from tbl1 where jsonb_exists(item, :elem)", nativeQuery = true)
List<Object[]> findBySingleElement(@Param("elem") String element);

// 位置参数写法
@Query("select * from tbl1 where jsonb_exists(item, ?1)", nativeQuery = true)
List<Object[]> findBySingleElement(String element);

2. 多元素任一匹配（替代?|操作符） #

使用jsonb_exists_any(jsonb_column, text[])函数，对应原查询item ?| '{elem21,elem1}'：

@Query("select * from tbl1 where jsonb_exists_any(item, array(:elems))", nativeQuery = true)
List<Object[]> findByAnyElement(@Param("elems") String[] elements);

// 位置参数写法
@Query("select * from tbl1 where jsonb_exists_any(item, array(?1))", nativeQuery = true)
List<Object[]> findByAnyElement(String[] elements);

原理说明 #

Spring Data JPA会将SQL语句中的?识别为参数占位符，而PostgreSQL的?、?|是JSONB操作符，两者语法冲突导致报错。使用对应的内置函数后，SQL中不再出现作为操作符的?，彻底避免了冲突问题，同时功能完全等价于原操作符。

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