postgresql - 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

In case escaping ? is not possible, you can create duplicate operator with different name.

New operator

Syntax for creating operators in Postgres:

CREATE OPERATOR name (
    PROCEDURE = function_name
    [, LEFTARG = left_type ] [, RIGHTARG = right_type ]
    [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]
    [, RESTRICT = res_proc ] [, JOIN = join_proc ]
    [, HASHES ] [, MERGES ]
)

In case of ?| used in jsonb it will be:

CREATE OPERATOR ^|(
  PROCEDURE = jsonb_exists_any,
  LEFTARG = jsonb,
  RIGHTARG = _text,
  RESTRICT = contsel,
  JOIN = contjoinsel);

I have used ^| as an example, alternative name. It can be any sequence from this list: + - * / < > = ~ ! @ # % ^ & | ?`.

You can find current definition for operator you are interested in by querying pg_catalog.pg_operator table.

SELECT oid, *
  FROM pg_catalog.pg_operator
 WHERE oprname = '?|'
   AND oprleft = (SELECT oid FROM pg_type WHERE typname = 'jsonb');

You can also use GUI tool like pgAdmin and browse pg_catalog to get SQL definition ready for reuse.

Enabling index

If you want to use index for this "new" operator, you will require to create new operator class and optionally family. In our case, we need both, since we can't add it to existing family, because default operator is already taking strategy slot.

Just like with operators, it is recommended to use GUI tool like pgAdmin to browse operator classes and just copy&paste it.

First, we take OID of operator we made duplicate of:

SELECT oid, *
  FROM pg_catalog.pg_operator
 WHERE oprname = '?|'
   AND oprleft = (SELECT oid FROM pg_type WHERE typname = 'jsonb');

Same thing for operator family (we will get it from operator class table instead), we are looking for gin class as this is the one that supports ?|. opcdefault is used, because there is optional class jsonb_path_ops that does not support this operator:

SELECT opcfamily
  FROM pg_opclass
 WHERE opcintype = (SELECT oid FROM pg_type WHERE typname = 'jsonb')
   AND opcmethod = (SELECT oid FROM pg_am WHERE amname = 'gin')
   AND opcdefault

Then we get strategy used by operator we duplicated:

SELECT amopstrategy,
       (SELECT typname FROM pg_type WHERE oid = amoplefttype) AS left_t, 
       (SELECT typname FROM pg_type WHERE oid = amoprighttype) AS right_t,*
FROM pg_amop
WHERE amopfamily = 4036 --family oid
  AND amopopr = 3248 --operator oid

Then functions used by class:

SELECT amprocnum, amproc::text, pg_get_function_identity_arguments(amproc::oid) AS args,
      (SELECT typname FROM pg_type WHERE oid = amproclefttype) AS left_t,
      (SELECT typname FROM pg_type WHERE oid = amprocrighttype) AS right_t,*
FROM pg_amproc
WHERE amprocfamily = 4036 --op family

This brings us to this operator class. It will create operator family if it does not exists already.

CREATE OPERATOR CLASS jsonb_ops_custom
   FOR TYPE jsonb USING gin AS
   OPERATOR 10  ^|(jsonb, _text),
   FUNCTION 1  gin_compare_jsonb(text, text),
   FUNCTION 2  gin_extract_jsonb(jsonb, internal, internal),
   FUNCTION 3  gin_extract_jsonb_query(jsonb, internal, smallint, internal, internal, internal, internal),
   FUNCTION 4  gin_consistent_jsonb(internal, smallint, jsonb, integer, internal, internal, internal, internal),
   FUNCTION 6  gin_triconsistent_jsonb(internal, smallint, jsonb, integer, internal, internal, internal);

Now you just need to create index using operator name that was created, something like:

CREATE INDEX ON jsonb_table USING gin(jsonb_column jsonb_ops_custom)

And you should be able to use index:

SET enable_seqscan = off;
EXPLAIN ANALYZE
SELECT * FROM jsonb_table WHERE jsonb_column ^| array['b', 'c'];