4

I have a json column in table like:

    @Type(type = "jsonb")
    @Column(name = "json_data", columnDefinition = "json")
    private List<Persion> jsonData = Collections.emptyList();

    public class Persion implements Serializable {
        private String name;

        private int age;

        private String sex;
    }

And I want to query rows contains specific name in json, I have write this code:

    predicates.add(
            criteriaBuilder.like(
                criteriaBuilder.treat(root.get("jsonData"), String.class),
                "%" + name + "%"
        )
    )

But it throws exception:

com.fasterxml.jackson.core.JsonParseException: Unexpected character ('%' (code 37)): expected a valid value (JSON String, Number, Array, Object or token 'null', 'true' or 'false')

Can I just read json as string and then use where condition to query? Any help would be appreciate.

MWiesner
  • 8,868
  • 11
  • 36
  • 70
Harry
  • 41
  • 2
  • 4

1 Answers1

2

You can try something like this

predicates.add(
        criteriaBuilder.like(
            criteriaBuilder.function("jsonb_extract_path_text", 
             String.class, root.get("jsonData"), 
             criteriaBuilder.literal("name")), "%" + name+ "%"
    )
)

I have found these links very helpful How do I use spring data jpa to query jsonb column? Build predicates for a postgres jsonb column with criteria builder using JPA criteria

sn183071m
  • 21
  • 1
  • thx first, The code you comment would work perfectly with json object, but mine is a json array. I alread change the table scheme. The question I asked was resolved. Thanks again. – Harry Jan 20 '22 at 03:11