如何转换PSQL :: json @> :: json转换为jpa/jpql-predicate [英] How to convert PSQLs ::json @> ::json to a jpa/jpql-predicate
问题描述
说我有一个看起来像这样的数据库表:
Say i have a db-table looking like this:
CREATE TABLE myTable(
id BIGINT,
date TIMESTAMP,
user_ids JSONB
);
user_ids
是JSONB-ARRAY
让此表的记录如下所示:
Let a record of this table look like this:
{
"id":13,
"date":"2019-01-25 11:03:57",
"user_ids":[25, 661, 88]
};
我需要查询user_ids包含25的所有记录.在SQL中,我可以使用以下选择语句来实现它:
I need to query all records where user_ids contain 25. In SQL i can achieve it with the following select-statement:
SELECT * FROM myTable where user_ids::jsonb @> '[25]'::jsonb;
现在,我需要编写一个JPA-Predicate,将"user_ids::jsonb @> '[25]'::jsonb"
呈现为休眠的可解析/可执行标准,然后在session.createQuery()
语句中使用该标准.
简单来说,我需要知道如何将PSQL片段(user_ids::jsonb @> '[25]'::jsonb)
编写为HQL表达式.
Now i need to write a JPA-Predicate that renders "user_ids::jsonb @> '[25]'::jsonb"
to a hibernate parseable/executable Criteria, which i then intent to use in a session.createQuery()
statement.
In simpler terms i need to know how i can write that PSQL-snippet (user_ids::jsonb @> '[25]'::jsonb)
as a HQL-expression.
推荐答案
幸运的是,PostgreSQL中的每个比较运算符都只是函数的别名,您可以在psql
控制台中键入\doS+
并找到别名.运算符(尽管在此搜索中某些运算符被认为是通配符,所以它们给出的结果超出预期).
Fortunately, every comparison operator in PostgreSQL is merely an alias to a function, and you can find the alias through the psql
console by typing \doS+
and the operator (although some operators are considered wildcards in this search, so they give more results than desired).
这是结果:
postgres=# \doS+ @>
List of operators
Schema | Name | Left arg type | Right arg type | Result type | Function | Description
------------+------+---------------+----------------+-------------+---------------------+-------------
pg_catalog | @> | aclitem[] | aclitem | boolean | aclcontains | contains
pg_catalog | @> | anyarray | anyarray | boolean | arraycontains | contains
pg_catalog | @> | anyrange | anyelement | boolean | range_contains_elem | contains
pg_catalog | @> | anyrange | anyrange | boolean | range_contains | contains
pg_catalog | @> | box | box | boolean | box_contain | contains
pg_catalog | @> | box | point | boolean | box_contain_pt | contains
pg_catalog | @> | circle | circle | boolean | circle_contain | contains
pg_catalog | @> | circle | point | boolean | circle_contain_pt | contains
pg_catalog | @> | jsonb | jsonb | boolean | jsonb_contains | contains
pg_catalog | @> | path | point | boolean | path_contain_pt | contains
pg_catalog | @> | polygon | point | boolean | poly_contain_pt | contains
pg_catalog | @> | polygon | polygon | boolean | poly_contain | contains
pg_catalog | @> | tsquery | tsquery | boolean | tsq_mcontains | contains
(13 rows)
您想要的是两侧的jsonb参数,我们看到的函数称为jsonb_contains
.因此,与jsonbcolumn @> jsonbvalue
等效的是jsonb_contains(jsonbcolumn, jsonbvalue)
.现在,除非在使用Hibernate的情况下通过自定义方言注册它,否则就不能在JPQL或CriteriaBuilder中使用该函数.如果您使用的是EclipseLink,我不知道那里的情况.
What you want is jsonb arguments on both sides, and we see the function that has that is called jsonb_contains
. So the equivalent to jsonbcolumn @> jsonbvalue
is jsonb_contains(jsonbcolumn, jsonbvalue)
. Now you can't use the function in either JPQL or CriteriaBuilder, unless you register it through a custom Dialect if you're using Hibernate. If you're using EclipseLink, I don't know the situation there.
从这里开始,您的选择是使用本机查询,或通过扩展现有的本地查询来添加自己的Hibernate方言.
From here on, your options are to use native queries, or add your own Hibernate Dialect by extending an existing one.
这篇关于如何转换PSQL :: json @> :: json转换为jpa/jpql-predicate的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!