Usage of PostgreSQL function "jsonb_path_exists" in jpa criteria api-CodePudding

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?

CodePudding user response：

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'.