3

Say i have a db-table looking like this:

CREATE TABLE myTable(
   id BIGINT, 
   date TIMESTAMP, 
   user_ids JSONB 
);

user_ids are a 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]
};

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;

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.

INeedHelp
  • 43
  • 6

2 Answers2

10

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).

Here is the result:

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)

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.

From here on, your options are to use native queries, or add your own Hibernate Dialect by extending an existing one.

coladict
  • 4,799
  • 1
  • 16
  • 27
  • 1
    Thank you very much Brother. First for this upmost accurate answer and second for the very good hint of \doS+ – INeedHelp Apr 12 '19 at 09:38
  • Is is possible to use index in this approach, after switching from operator to function, the index is not used – Barry Apr 11 '23 at 00:21
  • @Barry Last I checked the functions can't use indexes. However you can see this answer https://stackoverflow.com/a/64279340/3841161 where I didn't go far enough, and you can just define your own function implementing the `SQLFunction` and use the operator in the `render` function which has to transform it to native SQL. – coladict Apr 13 '23 at 11:54
0

Replacing "@>" with "jsonb_contains()" is not a good idea. The operator is indexed, not the function. Example: https://dbfiddle.uk/-xMuHYAA

FranckPachot
  • 414
  • 4
  • 10