postgresql - How to do parameter replacement within single quote for @@ postgres operator

Question

I am having issues with combining couple of different things together. I have tried a lot of things suggested on SO but nothing worked, hence posting my question.

So the requirements are

1. I need to build the dynamic query (to search jsonb column type) in Java
2. I need to use prepared statements of Java (to avoid sql injection)
3. I need to replace parameter which is inside single quotes

This query in CLI works perfectly

select * from items where cnt @@ '$.**.text like_regex "#finance" flag "i"';                                                                    

The bit that I want to parameterise is "#finance". So when I build this query

select * from items where cnt @@ '$.**.text like_regex ? flag "i"';                                                                    

When executing I get following error

"The column index is out of range: 1, number of columns: 0." 

It's because ? is within single quote so jdbc driver is not able to identify as replaceable parameter perhaps.

The closest question/discussion I could find is in this post: JDBC Prepared statement parameter inside json.

I have tried it however this does not work for me for some reason. They query now I run is

select * from items where cnt @@ ?::jsonb 

but with this I get following error

org.postgresql.util.PSQLException: ERROR: operator does not exist: jsonb @@ jsonb
  Hint: No operator matches the given name and argument types. You might need to add explicit type casts.
  Position: 106

I have tried various other ways like escaping single quotes etc but nothing really worked. I have very limited knowledge of PostgreSQL, so any help would be appreciated.

Postgres version: 13.1
Java version: 15

Answer 1

select * from items where cnt @@ ?::jsonb

The thing on the right of the @@ needs to be a jsonpath, not jsonb. So casting it to jsonb is clearly wrong. Try casting it to jsonpath instead.