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;
}

Nikolai Shevchenko · Answer 1 · 2019-08-05T06:15:01.740

10

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

edited Aug 05 '19 at 06:15

answered Aug 05 '19 at 06:08

Nikolai Shevchenko

7,083
8
33
42

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 Aug 07 '19 at 02:45
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. – Niv Aug 07 '19 at 02:53
@Niv you can combine predicates using `cb.and()` and `cb.or()` – Nikolai Shevchenko Aug 07 '19 at 04:02
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()` – Nikolai Shevchenko Aug 07 '19 at 04:15
Shevchenko, actually my json has numbers for number. some examples below: {"name": "John Doe", "number": 123} {"name": null, "number": null} – Niv Aug 07 '19 at 05:34
@Niv did you try replacing `String.class` with `Integer.class` for `inJsonNumbers`? – Nikolai Shevchenko Aug 07 '19 at 07:17
@nikolay-shevchenko any idea how to get this to work for int / Integer fields? – Niv Aug 12 '19 at 00:06
@Niv, no. I can't check. Maybe this would be helpful https://stackoverflow.com/q/9394176/2224047 – Nikolai Shevchenko Aug 13 '19 at 04:42
Shevchenko , do u have any idea how to get this working with Boolean objects ? – Niv Apr 07 '20 at 00:03
3

@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? – Arbaz Sheikh Nov 17 '21 at 08:04
Does anyone know how this works with json array? – Grancein Jun 01 '23 at 13:44

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

1 Answers1

Linked