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

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 — damike
– damike, Commented Aug 21, 2021 at 12:57

soung · Accepted Answer · 2021-05-17 02:07:14Z

6

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.

answered May 17, 2021 at 2:07

soung

1,62417 silver badges42 bronze badges

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

1 Comment

Oleksandr Lykhonosov Over a year ago

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

Adi M · Accepted Answer · 2021-07-14 14:20:32Z

0

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

answered Jul 14, 2021 at 14:20

Adi M

91 bronze badge

Comments

alex-kar · Accepted Answer · 2024-09-27 23:26:57Z

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

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related