Build predicates for a postgres jsonb column with criteria builder using JPA criteria

Question

I need to add a predicate to my list of existing predicates for a JSONB column.

Entity:

@Entity
@Table(name = "a")
@TypeDefs({
        @TypeDef(name = "jsonb", typeClass = JsonBinaryType.class),
})
public class EntityA {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "a_id_seq")
    @SequenceGenerator(sequenceName = "a_id_seq", allocationSize = 1, name = "a_id_seq")
    @Column(name = "id")
    private long id;

    @Column(name = "name")
    private String name;

    @Column(name = "json")
    @Type(type = "jsonb")
    private Json json;

    private static class Json {
        private String name;

        private Integer number;

        private String random;
    }
}

Specification:

    public Specification<EntityA> buildSpecification(Filter filter){

            return (root, query, cb) -> {
                        List<Predicate> predicates = new ArrayList<>();


            Expression<String> nameExpression = root.get("name");

        Predicate namePredicate = nameExpression.in(filter.getNames());
                            predicates.add(namePredicate);

//TODO add a predicate for JSONB here


                        return cb.and(predicates.toArray(new Predicate[0]));
                    };

My input will be a List jsonNames or List jsonNumbers and I want to build CriteriaBuilder.In with this input and fetch any matches.

Filter:

@Data
public class Filter {
    private List<String> names;

    private List<String> jsonNames;

    private List<Integer> jsonNumbers;
}

Answer 1

For PostgreSQL

Predicate inJsonNumbers = cb
        .function("jsonb_extract_path_text", 
                String.class, 
                root.get("json"), 
                cb.literal("number"))
        .in(filter.getJsonNumbers())

Predicate inJsonNames = cb
        .function("jsonb_extract_path_text", 
                String.class, 
                root.get("json"), 
                cb.literal("name"))
        .in(filter.getJsonNames())