CODEWITHSUNDEEP
sqlpostgresqlspring-data-jpajsonb
Oleksandr Lykhonosov
Oleksandr Lykhonosov

Reputation: 1378

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

Upvotes: 6

Views: 7354

Answers (3)

alex-kar
alex-kar

Reputation: 23

I faced the same issue using jsonb_exists_any function with Spring Data. To avoid additional brackets ARRAY[(?, ?)] I changed method signature to accept String[] instead of Set or any Collection:

@Query(value = "select * from users where jsonb_exists_any(opts, :values)", nativeQuery = true)
List<Users> findUsers(@Param("values") String[] values);

and added conversion into array in Service layer:

// Set<String> opts = Set.of("a","b","c");
repository.findUsers(opts.toArray());

Once it is array, you can apply any other functions, for example use filter only if array is not empty:

@Query(value = "select * from users 
                where (array_length(:values, 1) is null or 
                jsonb_exists_any(opts, :values))"

Upvotes: 0

Adi M
Adi M

Reputation: 9

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);

Upvotes: 0

soung
soung

Reputation: 1614

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.

Upvotes: 6

Related Questions