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

user_5600656 0
Viewed 2332

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 ?, ?

jsonb spring-data-jpa postgresql sql
2 Answers

can you try this :

@Query(value = "select * from users where jsonb_exists_any(opts, string_to_array(?1, ','))", nativeQuery = true)
List<Users> findUsers(String listStringSeparatedByComma);

Notice you have to replace the Set parameter by a String.

user_31430090

this will work:

@Query(value = "with array_query as (select array_agg(value) as array_value from (select (json_each_text(row_to_json(row_values))).value from (values ?1) row_values) col_values)"+
" select * from users where jsonb_exists_any(opts, (select array_value from array_query))", nativeQuery = true)
List<Users> findUsers(Set<String> opts);
user_164475280
Related