Equivalent criteriaBuilder method for @> jsonb

Question

I have a jsonb type column in postgres Table and need to compare the array of values. Trying to build Predicates using specification. Using like method of criteriaBuilder, it is giving couldnot extract result set error. In postgres table directly if I use @> able to get the result, can someone help to identify What is the criteriaBuilder method equivalent for @> for Postgres jsonb column instead of like?

Thanks. How to pass list of values in native query for @> argument — user3212324, Jun 22 '21 at 06:42
you can try referring to below solutions https://stackoverflow.com/questions/36041784/postgresql-compare-two-jsonb-objects — CodeLearnerSQL, Jun 22 '21 at 13:21

Ron · Answer 1 · 2022-06-19T15:14:40.890

I solved this issue by creating a function which calculating the @> operator, then I used it in CriteriaBuilder.

it's also useful for any other operator.

Create the following function in postgresql.

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

Then build a Predicate with CriteriaBuilder function.

JSONB {
    public <T> Predicate build(Root<T> root, CriteriaBuilder criteriaBuilder , FilterRequest request, Predicate predicate) {
        Expression<Boolean> key = criteriaBuilder.function("jsonb_contains_filter",
                Boolean.class,
                root.get(request.getColumnName()), // JSONB column from DB (a var)
                criteriaBuilder.literal(request.getFilterValue()) // JSON contains filter (b var)
        );
        return criteriaBuilder.and(cb.isTrue(key), predicate);
    }
}

UPDATE:

I found this Answer useful which may redundant the function creation.

You can use jsonb_contains function, but it must get two JSONB objects.

Equivalent criteriaBuilder method for @> jsonb

1 Answers1