I'm trying to fetch the rows that have at least one matching element in the jsonb array field from an input list. jsobb_column of table_a is a jsonb field that stores values in the form of an array.
The following sql query works correctly:
SELECT *
FROM table_a
where table_a.jsobb_column ?| array['item1', 'item2'];
The JPA query would look like this:
"select distinct table_a.* FROM table_a where table_a.jsonb_column ?| array[?0] "
Since JPA is not able to parse the syntax "?|" used for the jsonb operator, an error is thrown:
nested exception is java.lang.IllegalArgumentException: Mixing of ? parameters and other forms like ?0 is not supported!
I tried two different approaches to solve this problem:
Use "ESCAPE" to escape the "?" character, but it didn't work as expected.
Transfer one part of the query as a string parameter. The JPA query looks like follows:
"select distinct table_a.* FROM table_a where table_a.jsonb_column ?0 "
where the parameter at position 0 is a string variable with the following value: "?| array['item1', 'item2']"
In this case I receive the following error:
Caused by: org.postgresql.util.PSQLException: ERROR: syntax error at or near "$0"
DB: postgresql
Questions:
- How to escape the "?|" operator, so that the query will be executed?
- Is there another way to build the query to accieve the same result?
CodePudding user response:
I managed to find a solution to fix the query in JPA. The solution was simply to use the function jsonb_exists_any(). The query would look like this:
"select distinct table_a.* FROM table_a where jsonb_exists_any(table_a.jsonb_column, array[?0]) = true "