Usage of PostgreSQL function "jsonb_path_exists" in jpa criteria api

Question

I would like to call the postgresql function jsonb_path_exists (https://www.postgresql.org/docs/12/functions-json.html) using JPA criteria api.

Lets assume i have the following query:

select id from person where jsonb_path_exists(person.json,'$.some_property[0].typ ? (@ =="Something")');

Via the CriteriaBuilder i would do something like:

        var jsonQuery = jsonPath + " ? (@ ==\"" + value + "\")"; // evaluates to '$.some_property[0].typ ? (@ =="Something")'
        criteriaBuilder.function("jsonb_path_exists",
                        String.class,
                        entityRoot.get("json"),
                        criteriaBuilder.literal(jsonQuery)
                )
                .in(Boolean.TRUE);

However i have not figured out how to cast the jsonQuery which is provided as string to postgres jsonpath type. Hence i receive the following exception:

org.postgresql.util.PSQLException: ERROR: function jsonb_path_exists(jsonb, character varying) does not exist

The correct signature is jsonb_path_exists(target jsonb, path jsonpath [, vars jsonb [, silent bool]])

This issue exists for all functions with a jsonpath parameter e.g. jsonb_path_query,jsonb_path_match,jsonb_path_query_first

Does anybody have a clue on how to solve this?

Georg Moser · Accepted Answer · 2022-08-08 15:57:12Z

5

I have solved this issue by writing a PostgreSQL wrapper function which I now call instead of jsonb_path_exists:

CREATE OR REPLACE FUNCTION jsonb_filter(target jsonb, path varchar)
  RETURNS boolean
  LANGUAGE plpgsql IMMUTABLE STRICT cost 1 AS
$func$
  BEGIN
      RETURN jsonb_path_exists(target,CAST(path AS jsonpath));
  END
$func$;

Calling the created jsonb_filter function does the job:

        return criteriaBuilder.function("jsonb_filter",
                String.class,
                entityRoot.get("json"),
                jsonPathExpression
        ).in(Boolean.TRUE);

Make sure not to include surrounding single quotes in the jsonPathExpression, e.g. use $.your_expression instead of '$.your_expression'.

answered Aug 8, 2022 at 15:57

Georg Moser

7258 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

struberg Over a year ago

+1 The reason is that any JPA container just passes this down to the server via JDBC. And there is no PostgreSQL native JSON type (like it exists in some other Databases, but ofc highly proprietary). The PostgreSQL JDBC driver also does not understand the javax.json.JsonPointer data type. Thus it will always be passed down as java.lang.String. It might be considered as bug in PostgreSQL from a user POV, but it is ok-ish behaviour from a pure Java/DB/JPA perspective.

Collectives™ on Stack Overflow

Usage of PostgreSQL function "jsonb_path_exists" in jpa criteria api

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related