Search operation in jsonb column using Spring data jpa and CriteriaQuery

Question

I have one Postgres SQL query that will perform search operation inside array of values of the defined key inside jsonb type column.

SELECT o.id
FROM customer c INNER JOIN order o
on c.id = o.c_id where EXISTS (
    SELECT 1
    FROM jsonb_array_elements_text(c.customer_data->'sid') AS value
    WHERE value = '3456' OR value='89110'
); 

Here, sid is my key and having array of values. The given query will perform deep search inside array of values and return the records if key matching any of these value.

I want to generate its equivalent in spring data jpa that will return the same result using criteria builder and criteria query. Any suggestion regarding the same will be helpful?

My both the entities are join with one to many relationship and I am trying to achieve above query result:

public List<Long> findCustomerIds(EntityManager entityManager,String key, List<String> sidValues) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Long> cq = cb.createQuery(Long.class);
    Root<Customer> cRoot = cq.from(Customer.class);
    
    // Join between Customer and Order
    Join<Customer, Order> oJoin = cRoot.join("orders");

    // Subquery to handle JSONB array elements condition
    Subquery<String> subquery = cq.subquery(String.class);
    Root<Customer> subRoot = subquery.from(Customer.class);
    Path<Object> subCustomerDataPath = subRoot.get("customerData");
    Path<String> subSidPath = cb.function("jsonb_array_elements_text", String.class, subCustomerDataPath.get(key));

    subquery.select(subRoot.get("id"))
            .where(cb.or(
                sidValues.stream()
                              .map(sid -> cb.equal(cb.upper(subSidPath), sid.toUpperCase()))
                              .toArray(Predicate[]::new)
            ));

    cq.select(cRoot.get("id"))
      .where(cb.equal(oJoin.get("customer"), cRoot), cb.exists(subquery));

    return entityManager.createQuery(cq).getResultList();
}

The issue is in given portion of code:

// Subquery to handle JSONB array elements condition
Subquery<String> subquery = cq.subquery(String.class);
Root<Customer> subRoot = subquery.from(Customer.class);
Path<Object> subCustomerDataPath = subRoot.get("customerData");
Path<String> subSidPath = cb.function("jsonb_array_elements_text", String.class, subCustomerDataPath.get(key));
subquery.select(subRoot.get("id"))
.where(cb.or(
sidValues.stream()
   .map(sid -> cb.equal(cb.upper(subSidPath), sid.toUpperCase()))
                              .toArray(Predicate[]::new)
));

I am getting Illegal attempt to dereference path source [null.customerData] error in the subquery portion. I am not getting what is wrong in my code.

For reference I had defined the jsonb field in following manner in entity as all key and values are stored in following way:

@Type(type = "jsonb")
@Column(name = "customer_data",columnDefinition = "jsonb")
private Map<String,List<String>> customerData = new HashMap<>();

If you have any alternative way or any suggestion for the same will be helpful. Thank you in advance for your help.

Tyeshatyg

Why would you do that and not use a native query in JPA? It would be much easier, cleaner and readable. – Burble

Yeah, correct. But based on my requirement I want to work on multiple dynamic filters. In that case native queries will not work that appropriately. – Tyeshatyg

But you can do that quite nicely with generated native queries too. – Burble

Sorry to say but for my use case CriteriaQuery gives little bit more flexibility as I need to deal with multiple dynamic filters using single api. The above example is one of the exceptional scenario where I am facing some issues using CriteriaQuery. – Tyeshatyg

Answer 1

There are some issue with jsonb_array_element_text expression when applied with the function in Hibernate Criteria.

The given query can be used as an alternative for the above query to achieve the same result:

SELECT o.id
FROM customer c INNER JOIN order o
on c.id = o.c_id where LIKE c.customer_data->'sid' LIKE '%"3456"%' OR c.customer_data->'sid' LIKE '%"89110"%'

NOTE: Internally the stored array value will be converted into the string and search operation performed on the string. If the value is array of string than make sure to pass the quotes otherwise it will not work as expected.

To implement the same using hibernate criteria make use of jsonb_extract_path_text function.

public List<Long> findCustomerIds(EntityManager entityManager,String key, List<String> sidValues) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Long> cq = cb.createQuery(Long.class);
    Root<Customer> cRoot = cq.from(Customer.class);
    
    // Join between Customer and Order
    Join<Customer, Order> oJoin = cRoot.join("orders");

    // Constructing the WHERE clause predicates
    Predicate[] predicates = new Predicate[values.size()];
    for (int i = 0; i < values.size(); i++) {
predicates[i]= cb.like(cb.function("jsonb_extract_path_text", String.class, oJoin.get("customerData"), cb.literal(key)),"%\"" + values.get(i) + "\"%");
 }

    // Applying OR condition
    requestPredicate= cb.or(predicates);

    cq.select(cRoot.get("id")).where(requestPredicate);


    return entityManager.createQuery(cq).getResultList();
}

Pass the quotes externally for the value to be searched if the values are stored in list of string format otherwise it will work as expected.

Tyeshatyg