Search jsonb field using criteria API

Question

I have an entity in which there is a content field of type jsonb. Complex json is stored in this field. I need to make a selection on this field. I use criteria API. Such a query works:

public Predicate getPredicate(){
        Expression<String> function = builder.function("jsonb_extract_path_text",
                String.class,
                root.<String>get("content"), 
                builder.literal("unit"),
                builder.literal("id")
        );
        return builder.like(
                function,
                "%" + value + "%"
        );
}

JSON looks like this:

{
 "unit":{
   "id":"58bd51815744bf06e001b57b",
   "name":"my name",
   "another":{
     "anotherId":"45545454"
   }
 }
}

The problem is that the search conditions for JSON can be completely different. Maybe there is a search: unit.id = 58bd51815744bf06e001b57b. It could be like this: unit.another.anotherId = 45545454. And maybe even this: unit.id = 58bd51815744bf06e001b57b and unit.name = "my name".

I thought I could solve my problem something like this:

Expression<String> function = builder.function("jsonb_extract_path_text",
        String.class,
        root.<String>get("content"),
        builder.literal("$.unit.id")
);

But for some reason this does not work. Any ideas how to fix this?

Answer 1

I found a solution: So I do a search using Spring data

EntityAbstract one = entityRepository.findOne(((Specification<EntityAbstract>)
        (root, query, builder) -> {
            Expression<?>[] expressions = {root.<String>get("content"), builder.literal("unit"), builder.literal("id")};
            return toPredicate(root, query, builder, projectionName, expressions);
        }
)).orElseThrow(() -> new ResourceNotFoundException("Entity not found"));

And this is how the method with Criteria Builder looks like

public Predicate toPredicate(Root<EntityAbstract> root, CriteriaQuery<?> query, CriteriaBuilder builder, String value, Expression<?>[] args) {
    Expression<String> function = builder.function("jsonb_extract_path_text",
            String.class, args
    );
    return builder.like(
            function,
            "%" + value + "%"
    );
}

That is, I need to form an array (expressions) of the desired length, and this is not difficult