Search code examples
springjpaspring-data-jpajsonb

Can't parse jsonb Operator in JPA

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:

  1. Use "ESCAPE" to escape the "?" character, but it didn't work as expected.

  2. 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:

  1. How to escape the "?|" operator, so that the query will be executed?
  2. Is there another way to build the query to accieve the same result?
Solution

  • 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 "