How to create a JPA Predicate using complex Jsonb Postgresql column?

Question

Basically, I have a Specification in order to filter a query. Inside of it, a new predicate needs to be added.

Imagine a table (Z) with a jsonb column (bar). The column has the following structure:

{
  "foo": {
    "A": {...},
    "B": {...},
    "C": "..."
  }
}

The objective for the query is to check if foo contains any value from an array of strings. The query is this:

select *
from Z
where jsonb_exists_any(
   (select to_jsonb(
      array_to_json(src1.field_1)
   )
   from (
      select array_agg(src.field_1) as field_1
      from (select jsonb_object_keys(
         to_jsonb(jsonb_extract_path(
            Z.bar,
            'foo'))
         ) as field_1) src
      ) src1
   ),
array ['A','B']);

The main problem is converting this to a JPA predicate using the provided params for the Specification (CriteriaQuery, CriteriaBuilder and Root).

Been trying to use CriteriaBuilder functions but have only been successful on this subquery:

select jsonb_object_keys(
   to_jsonb(jsonb_extract_path(
      Z.bar,
      'foo')
   )
)

By doing this:

criteriaBuilder.function(
   "jsonb_object_keys",
   Object.class,
   criteriaBuilder.function(
      "to_jsonb",
      Object.class,
      criteriaBuilder.function( "jsonb_extract_path",
         Object.class,
         root.get( "bar" ),
         criteriaBuilder.literal( "foo" ) )
   ) 
)

Any suggestions?

Unrelated to your actual question: if you want to check if `foo` contains the **keys**, `'A'` or `'B'` your whole query can be simplified to `select * from Z where (bar -> 'foo') ?| array['A', 'B']` — , Jun 29 '20 at 16:46
Thanks for the reply. I know that, but unfortunately I could not make predicates work with operators, so I had to use functions. — Yikui17, Jun 29 '20 at 16:59
The joys of obfuscation layers. They make all the advanced stuff next to impossible. The operator could make use of an index, but your function solution will never be able to use an index — , Jun 29 '20 at 17:38

score 0 · Answer 1 · answered Aug 21 '20 at 16:49

Your example is unfortunately not very understandable to me. In my humble opinion that query can and should be simplified. None the less there is a way to get jsonb_extract_path (and other jsonb functions) into a JPA predicate (using hibernate as an implementation).

Let's say u want to retrieve from your z table the record(s) where bar.foo exists.

The sql for it would look like this:

select * from z where jsonb_extract_path(bar, 'foo') is not null;

To get this query into a JPA predicate you will need to write an own predicate that extends

org.hibernate.query.criteria.internal.predicate.AbstractSimplePredicate

and implements

org.hibernate.query.criteria.internal.expression.BinaryOperatorExpression

import org.hibernate.query.criteria.internal.CriteriaBuilderImpl;
import org.hibernate.query.criteria.internal.ParameterRegistry;
import org.hibernate.query.criteria.internal.Renderable;
import org.hibernate.query.criteria.internal.compile.RenderingContext;
import org.hibernate.query.criteria.internal.expression.BinaryOperatorExpression;
import org.hibernate.query.criteria.internal.expression.LiteralExpression;
import org.hibernate.query.criteria.internal.predicate.AbstractSimplePredicate;

import javax.persistence.criteria.Expression;
import java.io.Serializable;

import static java.lang.String.format;


public class ExtractJsonPath extends AbstractSimplePredicate implements
    BinaryOperatorExpression<Boolean>, Serializable {

   private final Expression<?> leftHandSide;

   private final Expression<?> rightHandSide;

   public ExtractJsonPath(CriteriaBuilderImpl criteriaBuilder, Expression<?> leftHandSide, Expression<?> rightHandSide) {
       super(criteriaBuilder);
       this.leftHandSide = leftHandSide;
       this.rightHandSide = rightHandSide;
   }


   @Override
   public Expression getLeftHandOperand() {
      return leftHandSide;
   }

   @Override
   public Expression getRightHandOperand() {
       return rightHandSide;
   }

   @Override
   public void registerParameters(ParameterRegistry registry) {
       Helper.possibleParameter(getLeftHandOperand(), registry);
       Helper.possibleParameter(getRightHandOperand(), registry);
   }

   @Override
   public String render(boolean isNegated, RenderingContext renderingContext) {
       return format(
            " jsonb_extract_path(%s, %s) ",
            ((Renderable) getLeftHandOperand()).render(renderingContext),
            ((Renderable) getRightHandOperand()).render(renderingContext)
       );
    }
}

Somewhere in your code you will need to put a where-clause in a query:

  query.where(builder.isNotNull(
            new ExtractJsonPath(
                    (CriteriaBuilderImpl) builder,
                    root.get("bar"),
                    builder.literal("foo")
            ));

And so u could use that function in a JPA predicate. Note that you can deep nest that construct.. If you need to check that bar.foo.x exists, you'd write:

           new ExtractJsonPath(
                    (CriteriaBuilderImpl) builder,
                    new ExtractJsonPath(
                             (CriteriaBuilderImpl) builder,
                             root.get("bar"),
                             builder.literal("foo")
                    ),
                    builder.literal("x")
           ));

Simpler in sql, since you could just write:

select * from z where jsonb_extract_path(bar, 'foo', 'x') is not null;

At this point i would like to apologize for not fully adopting your example. I hope this is an acceptable suggestion (or at least a descent start) and wish you a lot of success.

How to create a JPA Predicate using complex Jsonb Postgresql column?

1 Answers1