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

Question

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

Rainbow

You were able to solve it? – Berthaberthe

@Berthaberthe I haven't found nothing better than soung's answer – Rainbow

I found a solution yesterday: CREATE OR REPLACE FUNCTION wrap_varchar(VARIADIC params VARCHAR[]) RETURNS SETOF VARCHAR[] AS $$ SELECT params; $$ LANGUAGE SQL; Then SELECT jsonb_exists_any('{"name": "Joe Smith", "age": 28, "sports": ["football"]}', wrap_varchar('age','address')); which works with spring data. You can replace 'age','address' with :filter – Berthaberthe

Answer 1

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.

Snitch

yeah, your solution works, but I'm curious if there is an option to somehow use a list or array without converting and joining values to a string – Rainbow

Answer 2

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

Jackie

Answer 3

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

Rouen