How to escape question mark (?) character with Spring JpaRepository

Question

Postgres defines additional jsonb Operators such as ?|.

However, using Spring JpaRepository query builder, interrogation character is always considered as a parameter, and I can't figure how to escape it (except inside a single quote string, but then the query is invalid).

Example:

@Query(value = "SELECT * FROM public.user u WHERE u.authorities ?| array['ROLE_1', 'ROLE_2']", nativeQuery = true)

Error:

java.lang.IllegalArgumentException: Unable to resolve given parameter name [1] to QueryParameter reference
    at org.hibernate.query.internal.QueryParameterBindingsImpl.resolveQueryParameter(QueryParameterBindingsImpl.java:520)
    at org.hibernate.query.internal.QueryParameterBindingsImpl.getQueryParameterListBinding(QueryParameterBindingsImpl.java:498)
    at org.hibernate.query.internal.AbstractProducedQuery.setParameterList(AbstractProducedQuery.java:560)

Is there a way to escape it, or a different solution to be able to use theses postgres native operators containing ? character.

Trying to escape it with ??| or \?| does not work currently.

Note: I also tried to use a custom dialect function, but it ends with the same issue.

Libraries:

• hibernate 5.2.16
• hibernate-jpa 2.1
• spring-data-jpa 2.0.6.RELEASE
• postgresql 42.2.2

Thanks for your responses guys!

Answer 1

You can use direct call to a function jsonb_exists_any(). So in your case it would be

jsonb_exists_any(u.authorities::jsonb, array['ROLE_1', 'ROLE_2'])