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())

• Niv: thanks @nikolay-shevchenko! this works for jsonNames but not for the jsonNumbers and throws the below error- Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet.. Caused by: org.postgresql.util.PSQLException: ERROR: operator does not exist: text = integer Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
• Niv: also, is it possible to combine both conditions in one predicate? I added a third field "random" but I need only matches with "name" and "number" as one predicate.
• Nikolai Shevchenko: @Niv you can combine predicates using cb.and() and cb.or()
• Nikolai Shevchenko: As for your exception -- probably that's because you store numbers as strings inside JSON. "name": "A", "number": "45" instead of "name": "A", "number": 45. If so, and if you don't wanna change the data, you should convert filter.getJsonNumbers() to list of String-s before passing it to cb.function()
• Arbaz Sheikh: @NikolaiShevchenko How to get this working for array objects. I want to get a field from json array in nested json. Is there any way or function to do that?