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"
}
}
]