Call jsonb_contains function (postgres) using JPA criteria and JsonBinaryType

Question

I have a JPA/Hibernate entity which has a JSONB column (using https://github.com/vladmihalcea/hibernate-types ) for storing a list of strings. This works fine so far.

@TypeDef(name = "jsonb", typeClass = JsonBinaryType.class)

@Type(type = "jsonb")
@Column(name = "TAGS", columnDefinition = "jsonb")
private List<String> tags;

Now I want to check if another string is contained in the list of strings.

I can do this by writing a native query and use the @> operator from Postgres. Because of other reasons (the query is more complex) I do not want to go in that direction. My current approach is calling the jsonb_contains method in a Spring Data specification (since the operator is just alias to this function), e.g. jsonb_contains('["tag1", "tag2", "tag3"]','["tag1"]'). What I am struggling with is, getting the second parameter right.

My initial approach is to also use a List of Strings.

    public static Specification<MyEntity> hasTag(String tag) {
        return (root, query, cb) -> {
            if (StringUtils.isEmpty(tag)) {
                return criteriaBuilder.conjunction();
            }
            Expression<Boolean> expression = criteriaBuilder.function("jsonb_contains",
                                                                      Boolean.class,
                                                                      root.get("tags"),
                                                                      criteriaBuilder.literal(List.of(tag)));
            return criteriaBuilder.isTrue(expression);
        };
    }

This results in the following error.

Caused by: org.postgresql.util.PSQLException: ERROR: function jsonb_contains(jsonb, character varying) does not exist
  Hinweis: No function matches the given name and argument types. You might need to add explicit type casts.
  Position: 375

It does know that root.get("tags") is mapped to JSONB but for the second parameter it does not. How can I get this right? Is this actually possible?

Evgeny Umansky · Accepted Answer · 2022-06-23 07:40:55Z

5

jsonb_contains(jsob, jsonb) parameters must be jsonb type.

You can not pass a Java String as a parameter to the function.
You can not do casting in Postgresql via JPA Criteria.
Using JSONObject or whatever does not help because Postgresql sees it as bytea type.

There are 2 possible solutions:

Solution 1

Create jsonb with jsonb_build_object(text[]) function and send it to jsonb_contains(jsonb, jsonb) function:

   public static Specification<MyEntity> hasTag(String tag) {
            
            // get List of key-value: [key1, value1, key2, value2...]
            List<Object> tags = List.of(tag);
            
            // create jsonb from array list
            Expression<?> jsonb = criteriaBuilder.function(
                    "jsonb_build_object",
                    Object.class,
                    cb.literal(tags)
            );

            Expression<Boolean> expression = criteriaBuilder.function(
                    "jsonb_contains",
                    Boolean.class,
                    root.get("tags"),
                    jsonb
            );

            return criteriaBuilder.isTrue(expression);
    }

Solution 2

Create custom function in your Postgresql and use it in Java:

SQL:

CREATE FUNCTION jsonb_contains_as_text(a jsonb, b text)
RETURNS BOOLEAN AS $$
  SELECT CASE
  WHEN a @> b::jsonb THEN TRUE
  ELSE FALSE
END;$$
LANGUAGE SQL IMMUTABLE STRICT;

Java Code:

   public static Specification<MyEntity> hasTag(String tag) {

            Expression<Boolean> expression = criteriaBuilder.function(
                   "jsonb_contains_as_text",
                    Boolean.class,
                    root.get("tags"),
                    criteriaBuilder.literal(tag)
            );

            return criteriaBuilder.isTrue(expression);
    }

edited Jun 23, 2022 at 7:40

answered Jun 19, 2022 at 17:57

Evgeny Umansky

1512 silver badges4 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Andrei Bardyshev Over a year ago

Solution 1 for me worked with jsonb_build_array: criteriaBuilder.function( "jsonb_build_array", Any::class.java, *ids.map { criteriaBuilder.literal(it) }.toTypedArray()). Sorry it's in kotlin

Alexander Golub Over a year ago

@AndreiBardyshev thx for your answer, *ids.map { criteriaBuilder.literal(it) }.toTypedArray()) - this is the key for me) In hibernate 5 criteriaBuilder.literal(tag) - was enough

ivanmc-007 · Accepted Answer · 2025-02-28 06:08:46Z

1

I was solving a similar problem (PostgreSQL). I agree with the author of the decision Evgeny Umansky. Perhaps this will be useful to someone else:

Predicate toGroupListPredicate(Root<Campaign> root, CriteriaBuilder cb) {
  Predicate predicate = cb.disjunction();
  // create jsonb array from list
  Expression<?> jsonb = cb.function(
    "jsonb_build_array",
    Object.class,
    cb.literal(filterDto.getGroupList())
  );
  
  Expression<Boolean> expression = cb.function(
    "jsonb_contains",
    Boolean.class,
    root.get("groupList"),
    jsonb
  );
  predicate.getExpressions().add(cb.isTrue(expression));
  return predicate;
}
// custom_filter
class FilterDto {
  private List<Long> groupList;
}

edited Feb 28 at 6:08

answered Feb 27 at 16:25

ivanmc-007

112 bronze badges

Comments

androberz · Accepted Answer · 2021-02-11 10:22:42Z

0

I think that the reason is that you pass the varchar as the second param. jsonb_contains() requires two jsonb params.

To check a jsonb array contains all/any values from a string array you need to use another operators: ?& or ?|.

The methods bindings for them in PSQL 9.4 are: jsonb_exists_all and jsonb_exists_any correspondingly.

In your PSQL version, you could check it by the following command:

select * from pg_operator where oprname = '?&'

answered Feb 11, 2021 at 10:22

androberz

95414 silver badges30 bronze badges

Collectives™ on Stack Overflow

Call jsonb_contains function (postgres) using JPA criteria and JsonBinaryType

3 Answers 3

Solution 1

Solution 2

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Solution 1

Solution 2

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related