Question 114416 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Bind JDBI parameter inside double quotes

I have a Dropwizard + Jdbi + Postgres 12 setup. I want to run following query with a SQL/Json Path Expression on a complex/nested JsonB column:

try (Handle handle = jdbi.open()) {
        return handle.createQuery(
                        "select data from company where" +
                                " data @?? '$.employees[*].assets.vehicle[*] ? (@.vehicleNumber like_regex ":regex")'"
                )
                .bind("regex", somRegex)
                .map(mapper)
                .collect(toList());
    }

The problem is that I am unable to bind the regEx paramenter inside double quotes inside Json Path Expression, and the query returns no result. The same query works when I run it directly in Postgres or hardcode the regex in the prepared query string.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I was able to make it work by using concat() to bind the param and then type cast it to jsonpath:

    try (Handle handle = jdbi.open()) {
    return handle.createQuery(
                    "select data from company where" +
                            " data @?? CAST(concat('$.employees[*].assets.vehicle[*] ? (@.vehicleNumber like_regex "',':regex','")') AS jsonpath)"
            )
            .bind("regex", somRegex)
            .map(mapper)
            .collect(toList());
}

  2. 0

    You cannot bind arguments inside string literals, this limitation comes from JDBC itself. See docs.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search