How use '?' in @Query? How use Jsonb of postgres in spring-boot at all?

Question

How to use ?| operator in postgres query in spring repository? I need to use where in my query for text type column which content json.

@Query(value =
        "SELECT * \n" +
        "FROM tbl t \n" +
        "WHERE t.some_ids::::jsonb ?| array['152960','188775']", nativeQuery = true
)
List<Model> getModelsByIds();

But that don't work and I catch the next exeception: org.springframework.dao.InvalidDataAccessApiUsageException: At least 1 parameter(s) provided but only 0 parameter(s) present in query.

Indeed, obfuscation layers do make it hard to use the full potential of Postgres — user330315
– user330315, Commented May 18, 2020 at 20:14

score 2 · Accepted Answer · 2020-05-19 07:15:58Z

2

You can use the associated function of that operator instead. Most of the time the obfuscation layers also choke on the :: cast operator, so you might want to use cast() instead:

WHERE pg_catalog.jsonb_exists_any(cast(t.some_ids as jsonb), array['152960','188775'])

However I think this wouldn't be able to make use of an index defined on some_ids::jsonb

You didn't mention how exactly the content of some_ids looks like.

If that is a JSON array (e.g. '["123", "456"]'::jsonb) then you can also use the contains operator @>:

WHERE cast(t.some_ids as jsonb) @> '["152960","188775"]'

If your JSON array contains numbers rather than strings ('[123,456]') you need to pass numbers in the argument as well:

WHERE cast(t.some_ids as jsonb) @> '[152960,188775]'

edited May 19, 2020 at 7:15

answered May 19, 2020 at 7:06

user330315

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

2 Comments

Anton Magov Over a year ago

Thank you. That is what I need. You explained very well. And... could you tell me how to pass the array as param in @Query?

Anton Magov Over a year ago

Tkanks, I resolved it using string_to_array(:ids, ',').

Collectives™ on Stack Overflow

How use '?' in @Query? How use Jsonb of postgres in spring-boot at all?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related