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 ?

Answer 1

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