postgresqljsonbdropwizardjsonpathjdbi

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.


Solution

  • 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());
    }