How to escape question mark (?) character with Spring JpaRepository
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'];
As a workaround for that specific case, I created a custom operator:
CREATE OPERATOR ~~~| (
LEFTARG = jsonb,
RIGHTARG = _text,
PROCEDURE = pg_catalog.jsonb_exists_any
)
Then in my query: WHERE u.authorities ~~~| array['ROLE_1', 'ROLE_2']
@ŁukaszKamiński detailed this workaround in his answer here: https://stackoverflow.com/a/50488457/1097926
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'])