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?

• Adrian Klaver: I don't use Spring JPA, but this Named Parameters looks like something worth trying.

Answer 1

I found the internal implementation of operation ?|, under the hood it invokes the procedure jsonb_exists_any(jsonb , text[]), so the possible solution is:

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

• razor: It doesn't exactly solve the problem, because when You use functions (instead of operators: ?, ?| etc.), Postgresql doesn't use indecies !!
• ashoka: this works perfectly at least on less amount of data.

Answer 2

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

• Nick: Thanks for comment! It doesn't work, there is the same error msg