[SQL] Spring Data JPA Native Query - How to use Postgres ARRAY type as a parameter

I have a native postgresql query (opts - jsonb array):

select * from table users jsonb_exists_any(opts, ARRAY['CASH', 'CARD']);


it works fine in my database console and I'm getting a result:

user1, ['CASH','CARD']
user2, ['CASH']
user3, ['CARD']


but when I want to use it in my spring data jpa application as:

@Query(value = "select * from users where jsonb_exists_any(opts, ARRAY[?1])", nativeQuery = true)
List<Users> findUsers(Set<String> opts);


I'm getting an error:

h.e.j.s.SqlExceptionHelper - SQL Error: 0, SQLState: 42883 h.e.j.s.SqlExceptionHelper - ERROR: function jsonb_exists_any(jsonb, record[]) does not exist
​

because that query converts to:

select
*
from
users
where
jsonb_exists_any(opts, ARRAY[(?, ?)])


Is there a way to pass parameters as an array? i.e. without brackets around ?, ?

Continue reading...