I am using Hibernate SpringBoot and have a @Query() with nativeQuery=true where I am attempting to use a Postgres jsonb function to find a row where a JSON list has an entry with a specific field value, where banana_id is a parameter:

WHERE jsonb_path_exists(CAST(foo.things as jsonb), '$[*] ? (@.id == :banana_id)')

but no matter what I try, the query fails. According to this post Using Postgres JSONB query with Spring Data and bind parameter fails with InvalidDataAccessApiUsageException it's impossible to inject into a json path query, so that's why :banana_id doesn't work, but I can't figure out how to reframe this without that.

CodePudding user response：

Ended up injecting the whole query as a string and casting it to jsonpath, with this:

jsonb_path_exists(CAST(foo.things as jsonb), CAST(:query_str as jsonpath))