如何使用复杂的Jsonb Postgresql列创建JPA谓词？

Question

基本上，我有一个规范，以便过滤一个查询。在它的内部，需要添加一个新的谓词。

想象一个带有jsonb列（bar）的表(Z)。该列具有以下结构：

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

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']);

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

通过这样做：

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

有什么建议吗？

Answer 1

你的例子很不幸我不太理解。以我的浅见，查询可以而且应该被简化。尽管如此，还是有一种方法可以将jsonb_extract_path（和其他jsonb函数）获取到一个JPA谓词中（使用hibernate作为实现）。

假设您想从z表中检索bar.foo所在的记录。

它的sql如下所示：

选择*from z，其中jsonb_extract_path(bar，'foo')不为空；

要将此查询转换为JPA谓词，您需要编写一个自己的谓词，该谓词扩展

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

和工具

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

在代码的某个地方,您需要在查询中放置WHERE子句：

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

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

在这一点上，我想为没有完全采纳你的榜样而道歉。我希望这是一个可以接受的建议（或者至少是一个下降的开始），并祝你取得很多成功。