Searching into postgres Jsonb column using CriteriaBuilder JPA

387 views Asked by At

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.

0

There are 0 answers