Bind JDBI parameter inside double quotes-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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