I am facing issues while retrieving/searching through Postgres JSONB column using CriteriaBuilder.
Postgres Query : SELECT * FROM table where lower(jsonb_extract_path(tags,'Case')::text) = lower('"normal"') as text));
Tags: datatype of tags column is JSONB.
Above query is working fine using postgres client.
But I was facing issues while trying to get it done from CriteriaBuilder.
Following is my code:
private Specification<TemplateMetadata> findByTagsFilter(Map<String, String[]> requestParameterMap) {
return (root, query, builder) -> {
List<Predicate> predicates = new ArrayList<>();
if (!StringUtils.isEmpty(requestParameterMap)) {
predicates = requestParameterMap.entrySet().stream()
.filter(keyValue1 -> validateKeyValues(keyValue1.getKey()))
.map(keyValue2 -> Arrays.asList(keyValue2.getValue()).stream()
.map(v -> builder.equal(builder.function("jsonb_extract_path", String.class, builder.lower(root.<String>get("tags")), builder.literal(keyValue2.getKey())) , builder.lower(builder.literal("\""+v+"\"")).as(JsonDataType.class)))
.flatMap(list -> list.distinct())
.collect(Collectors.toList());
}
return builder.and(
predicates.toArray(new Predicate[predicates.size()])
);
};
}
requestParameterMap is filled by REST (API URI should looks like : ?testtag1=testtag1&testtag2=testtag2)
I am getting follwoing Error
ERROR: function lower(jsonb) does not exist\n Hint: No function matches the given name and argument types. You might need to add explicit type casts.\n Position: 444
Can some one help me on this.