postgresql - Cannot use postgres jsonb operator ?| using spring jpa

Question

I need to search entities where at least one element from array exists in jsonb array. For this I can use an sql query:

select * from person where roles ?| array['ROLE_1','ROLE_2'];

But in case of spring jpa this is not valid:

@Query(value = "select * from person where roles ?| array['ROLE_1','ROLE_2']", nativeQuery = true)

The error is following:

At least 1 parameter(s) provided but only 0 parameter(s) present in query.

I understand that the problem is in special char ? which spring interpret as a required parameter in repository method (say there is method findRole1OrRole2()), but how I can handle that?

question from:https://stackoverflow.com/questions/65925288/cannot-use-postgres-jsonb-operator-using-spring-jpa

Answer 1

Since you are using the PostgreSQL JDBC driver, you should escape the question mark by doubling it: ??|