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?

Answer 1

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);
    }

Answer 2

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 = '?&'