How to create a native query in JPA

Question

I have a query that checks if in column jsonb that is List present a string like:

@Query(value = "SELECT item FROM catalog_cache WHERE tags ? 'somestring' ", nativeQuery = true)

Is there special syntax in JPA for jsonb operations like '?', '?|', '@>'.

Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$1"

But it doesn't work. Any ideas why?

it returns item. But it doesn't matter cause for JPA '?' is special symbol even if I set it nativeQuery. I need to JPA understand real native SQL JSONB syntax with |?, &?, ? — user3693537, Mar 20 '21 at 05:24

score 0 · Answer 1 · edited Mar 19 '21 at 16:01

0

correct your syntax it will be like this :

@Query(value = "SELECT * from catalog_cache WHERE where tags = ?1", nativeQuery = true)
List<CatalogCacheEntity> findWhatEverName(String value);

edited Mar 19 '21 at 16:01

gtiwari333

answered Mar 19 '21 at 15:58

Mahmoud Odeh

It doesn't work because tags is JSONB column. Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: jsonb = character varying – user3693537 Mar 20 '21 at 04:15
@user3693537 you have to be more specific, but for instance have a look here: https://stackoverflow.com/questions/43900457/how-do-i-use-spring-data-jpa-to-query-jsonb-column , there something called `jsonb_extract_path_text` – Mahmoud Odeh Mar 20 '21 at 10:10
If be more specific: I have a table that contains column jsonb tags. Tags is list structure. [ "x", "y", "z"]. I created index CREATE INDEX catalog_cache_tags_inx ON catalog_cache USING GIN ((tags)); So to work more effective I need execute that query SELECT * from catalog_cache WHERE where tags ? 'x'. to find all rows where column tags contains 'x' – user3693537 Mar 21 '21 at 07:41

1 Answers1