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?

I don't use Spring JPA, but this Named Parameters looks like something worth trying. — Adrian Klaver
– Adrian Klaver, Commented Jan 27, 2021 at 19:03

Nick · Accepted Answer · 2021-01-27 19:12:24Z

3

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)

answered Jan 27, 2021 at 19:12

Nick

2075 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

razor Over a year ago

It doesn't exactly solve the problem, because when You use functions (instead of operators: ?, ?| etc.), Postgresql doesn't use indecies !!

ashoka Feb 26 at 17:13

this works perfectly at least on less amount of data.

Laurenz Albe · Accepted Answer · 2021-01-27 18:39:25Z

1

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

answered Jan 27, 2021 at 18:39

Laurenz Albe

257k22 gold badges310 silver badges387 bronze badges

1 Comment

Nick Over a year ago

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

Collectives™ on Stack Overflow

Cannot use postgres jsonb operator ?| using spring jpa

2 Answers 2

2 Comments

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Related