title: How to select data in spring boot using jpa based on where condition of jsonb column of postgres(without native query)?
body: <p>I have Postgres database with jsonb field. I have used Predicate list for where condition with criteria builder in jpa. Now, I want to fetch JSON data stored in a database based on multiple where cause along with JSON. How it could be possible to do?</p>
  
  <p><a href="https://i.stack.imgur.com/l5SFn.png" rel="nofollow noreferrer">Database table</a></p>
  
  <pre><code>private List&lt;Predicate&gt; whereClause(CriteriaBuilder criteriaBuilder, Root&lt;KafkaLog&gt; kafkaLog,
    KafkaLogSearchDto search) {
  List&lt;Predicate&gt; predicates = new ArrayList&lt;&gt;();
  if (search.getTopic() != null &amp;&amp; !search.getTopic().isEmpty()) {
    Expression&lt;String&gt; literal =
        criteriaBuilder.literal("%" + search.getTopic().toUpperCase() + "%");
    predicates.add(criteriaBuilder.like(criteriaBuilder.upper(kafkaLog.get("topic")), literal));
  }
  if (search.getOffset() != null) {
    predicates.add(criteriaBuilder.equal(kafkaLog.get("offset"), search.getOffset()));
  }
  if (search.getResourceId() != null) {
    predicates.add(criteriaBuilder.equal(kafkaLog.get("invoiceId"), search.getResourceId()));
  }
  if (search.getPartition() != null) {
    predicates.add(criteriaBuilder.equal(kafkaLog.get("partition"), search.getPartition()));
  }
  if (search.getStatus() != null) {
    predicates.add(criteriaBuilder.equal(kafkaLog.get("status"), search.getStatus()));
  }
  if (search.getCreatedAtFrom() != null &amp;&amp; search.getCreatedAtTo() != null) {
    predicates.add(criteriaBuilder.and(
        criteriaBuilder.greaterThanOrEqualTo(kafkaLog.get("createdAt").as(Date.class),
            search.getCreatedAtFrom()),
        criteriaBuilder.lessThanOrEqualTo(kafkaLog.get("createdAt").as(Date.class),
            search.getCreatedAtTo())));
  }
  if (search.getPayload() != null &amp;&amp; !search.getPayload().isEmpty()) {
    Expression&lt;String&gt; literal =
        criteriaBuilder.literal("%" + search.getPayload().toUpperCase() + "%"); 
  
  
  
  }
  return predicates;
  </code></pre>
  
  <p>}</p>
  
  <pre><code>private CriteriaQuery&lt;KafkaLog&gt; getKafkaBySearchCriteria(KafkaLogSearchDto search, String orderBy,
    int sortingDirection) {
  CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
  CriteriaQuery&lt;KafkaLog&gt; criteriaQuery = criteriaBuilder.createQuery(KafkaLog.class);
  Root&lt;KafkaLog&gt; kafkaLog = criteriaQuery.from(KafkaLog.class);
  
  List&lt;Predicate&gt; predicates = whereClause(criteriaBuilder, kafkaLog, search);
  criteriaQuery.where(predicates.toArray(new Predicate[] {}));
  
  if (orderBy == null || orderBy.isEmpty()) {
    orderBy = "topic";
  }
  Expression&lt;?&gt; orderColumn = kafkaLog.get(orderBy);
  if (orderColumn != null) {
    if (sortingDirection == 0) {
      criteriaQuery.orderBy(criteriaBuilder.asc(orderColumn));
    } else if (sortingDirection == 1) {
      criteriaQuery.orderBy(criteriaBuilder.desc(orderColumn));
    }
  }
  return criteriaQuery;
  </code></pre>
  
  <p>}</p>