How to get json column as string in postgresql with jpa criteria builder?

Question

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.

• Soroush Shemshadi: Hi Harry, I read in the comments that you solved the problem. Can you help me, I have the same problem.

Answer 1

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

• Harry: 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.