如果无法转义 ?
,可以创建具有不同名称的重复运算符。
新运算符
在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 ]
)
如果在 jsonb
中使用 ?|
,它将是:
CREATE OPERATOR ^|(
PROCEDURE = jsonb_exists_any,
LEFTARG = jsonb,
RIGHTARG = _text,
RESTRICT = contsel,
JOIN = contjoinsel);
我已经使用
^|
作为一个例子,也可以是列表中的任何一个序列:
+ - * / < > = ~ ! @ # % ^ & |
?`。你可以通过查询
pg_catalog.pg_operator表来找到你感兴趣的运算符的当前定义。
SELECT oid, *
FROM pg_catalog.pg_operator
WHERE oprname = '?|'
AND oprleft = (SELECT oid FROM pg_type WHERE typname = 'jsonb');
您还可以使用GUI工具,如pgAdmin,并浏览pg_catalog
,以获取可重用的SQL定义。
启用索引
如果您要为此“新”运算符使用索引,则需要创建新的运算符类和可选系列。在我们的情况下,我们需要两者,因为我们无法将其添加到现有系列中,因为默认运算符已经占用了strategy slot。
就像操作符一样,建议使用GUI工具,如pgAdmin,浏览运算符类并复制&粘贴它。
首先,我们获取我们复制的运算符的OID:
SELECT oid, *
FROM pg_catalog.pg_operator
WHERE oprname = '?|'
AND oprleft = (SELECT oid FROM pg_type WHERE typname = 'jsonb');
同样适用于运算符族(我们将从
operator class table中获取它),我们正在寻找gin类,因为这是支持
?|
的类。使用
opcdefault
,因为有一个不支持此运算符的可选类
jsonb_path_ops
。
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
然后我们得到了运营商使用的策略,我们进行了复制:
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
AND amopopr = 3248
接下来是 类使用的函数:
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
这让我们来到了这个
操作员类。如果不存在,它将创建操作员家族。
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);
现在您只需要使用已创建的运算符名称创建索引,类似于以下内容:
CREATE INDEX ON jsonb_table USING gin(jsonb_column jsonb_ops_custom)
而且你应该能够使用索引:
SET enable_seqscan = off;
EXPLAIN ANALYZE
SELECT * FROM jsonb_table WHERE jsonb_column ^| array['b', 'c'];
?
。我认为这应该可以解决问题。所以将??|
作为运算符。它在JDBC中有效,所以我不确定它是否适用于JPA。(解决方案2)Execute是原始SQL,而不是准备好的语句。(解决方案3)为此操作符在PL/pgSQL中创建一个包装函数。但要注意,它需要功能索引才能在大量数据上快速工作。 - Kamil Gosciminski??|
代替?|
。 - user330315select * from pg_operator where oprname = '?|' and oprleft = (select oid from pg_type where typname = 'jsonb');
。更容易的方法是使用pgAdmin或其他工具,在浏览现有运算符(在pg_catalog中)时显示创建语句。 - Łukasz Kamiński