0

i am using criteriabuilder of jpa and i want to look for a value of a json field in a jsonb column. the jsonb looks like this : 

[{"field1":"value1","field2":"value2"},{"field3":"value3","field4":"value4"}]

i started creating my predicates but i got errors.

predicates.add( builder.like(
    builder.function("JSON_EXTRACT", String.class, root1.get("jsonBColumn"),                          
    builder.literal("\"value3\"")), "%" + searchValue + "%"));

the error i am getting is

org.postgresql.util.PSQLException: ERROR: function json_extract(jsonb, character varying) does not exist Hint: No function matches the given name and argument types. You might need to add explicit type casts.

Can anybody help ?

M.Simon
  • 11
  • 9
  • Here's an idea. Format your post so that it is half readable ... –  Jul 31 '18 at 06:12
  • ok i did format my post – M.Simon Jul 31 '18 at 14:17
  • 1
    And did you read the error message? "json_extract" doesn't exist, from your database. So perhaps, just perhaps there is no such function with that name. https://www.postgresql.org/docs/9.6/static/functions-json.html –  Jul 31 '18 at 14:21

1 Answers1

0

This will help:

`predicate.getExpressions().add(cb.like(cb.function("jsonb_extract_path_text", String.class, root.get("jsonBColumn"), cb.literal(this.searchKey)), "%"+ this.searchValue + "%"));

Kamaljit Sidhu
  • 19
  • 1