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