Is there any way to search within postgres JSONB array column using Criteria API?

Viewed 128

I want to create predicate that read and compare email, so I can filter out result using Criteria API in Java.

For that I used below code but didn't work. Error: Caused by: org.postgresql.util.PSQLException: ERROR: set-returning functions are not allowed in WHERE

Expression<String[]> expressions = criteriaBuilder.function("jsonb_array_elements",
                                String[].class, entity.<String>get(column_name)
                        );
                        Expression<?>[] expressions1 = {expressions, criteriaBuilder.literal("characteristic"), criteriaBuilder.literal("email")};
                        toPredicate(entity, searchText.toLowerCase(), expressions1);
                        
                        
public Predicate toPredicate(Root<TeamMember> root, String value, Expression<?>[] args) {
        Expression<String> function = criteriaBuilder.function("jsonb_extract_path_text",
                String.class, args
        );
        return criteriaBuilder.like(
                function,
                "%" + value + "%"
        );
    }

My JSOB object looks like below:

[
  {
    "id": "1234",
    "validFor": {
      "endDateTime": null,
      "startDateTime": "2018-08-12T06:54:31.610218300Z"
    },
    "preferred": true,    
    "characteristic": {
      "email": "abcd@gmail.com"
    }
  },
  {
    "id": "5678",
    "validFor": {
      "endDateTime": null,
      "startDateTime": "2019-09-15T06:54:31.610218300Z"
    },
    "preferred": true,    
    "characteristic": {
      "email": "xyz@gmail.com"
    }
  }
]
0 Answers
Related