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? — user330315
– user330315, Commented Jan 31, 2021 at 9: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
– Paolo bi, Commented Jan 31, 2021 at 10:52
Is it working? It's unclear what you're trying to achieve. Could you elaborate more? — Mansoor
– Mansoor, Commented Jan 31, 2021 at 13:55

i v s narayana · Accepted Answer · 2022-05-27 07:54:13Z

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

answered May 27, 2022 at 7:54

i v s narayana

1201 silver badge3 bronze badges

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

Collectives™ on Stack Overflow

Array contains into postgres jsonb with Spring data JDBC

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related