Array contains into postgres jsonb with Spring data JDBC

Question

Let's make a journey...

What I want to achieve is

SELECT * FROM people WHERE interest->'interests' ?| ARRAY['sport','cars'];

into my Repository defined as

public interface PeopleRepository extends CrudRepository<People, UUID> {
   @Query("SELECT * FROM people where interest->'interests' ??| :array::text[] ")
   List<People> findByInterest(@Param("array") String interest);

and finally select interests, using the method in this way

findByInterest("{foo, beer}")

This is what I achieved after a day of tests and IMHO is really MEH

I think out of there, a nicest solution is possible (without sql cast and string concatenation in runtime)

Could you help with a more "clean code" solution? (Sorry, for the long post)

Update

I will elaborate a little bit better my question. I'm searching for something like

@Query("SELECT * FROM people where interest->'interests' ??| :array::text[] ")
List<People> findByInterest(@Param("array") List<String> interest);

is this possible ?

It's easily possible in plain JDBC, don't know about Spring though. Does it work if you declare the Java parameter as an array, e.g. `findByInterest(@Param("array") String[] interest);` and use `??| :array` in the query? — , Jan 31 '21 at 09:45
That's is strange! I tested it out, and using the `String[]` no error during compilation, but in runtime no result will be retrieved, what da hell... But honestly also if this solution would work, I think we deserve a better code for this kind of stuff — Paolo bi, Jan 31 '21 at 10:52
Is it working? It's unclear what you're trying to achieve. Could you elaborate more? — Mansoor, Jan 31 '21 at 13:55

score 1 · Accepted Answer · answered May 27 '22 at 07:54

JSON array elements strores data in the following way

{ "interests" : ["sport", "cars"]

}

So if you directly java list it does not work, so one option is convert java array into a string which can be looked at as json array like ["sport", "cars"] or use jsonObject used to create json array like below

JSONArray interests = new JSONArray();
a.add(0, "sport");
a.add(0, "car");

findByInterest(interests.toString())

@Query("SELECT * FROM people where interest->'interests' ??| :array::text[] ")
List<People> findByInterest(@Param("array") String interest);

Array contains into postgres jsonb with Spring data JDBC

1 Answers1