CODEWITHSUNDEEP
postgresqlspring-boot
Anton Magov
Anton Magov

Reputation: 186

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

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.

Upvotes: 1

Views: 646

Answers (1)

user330315
user330315

Reputation:

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]'

Upvotes: 2

Related Questions