How do I use PostgreSQL JSON(B) operators containing a question mark "?" via JDBC
There are two possible workarounds:
Use static statements, instead of prepared statements
This is the simplest workaround, but you lose all the benefits from prepared statements (performance, SQL injection protection, etc.). However, this will work
try (Statement s = c.createStatement(); ResultSet rs = s.executeQuery("select '{}'::jsonb ?| array['a', 'b']")) { ...}
Avoid the operator. Use a function instead (note: indexes might not be used)
Operators are just syntax sugar for a backing function that exists in the pg_catalog
. Here's how to find the name of these functions:
SELECT oprname, oprcode || '(' || format_type(oprleft, NULL::integer) || ', ' || format_type(oprright, NULL::integer) || ')' AS functionFROM pg_operator WHERE oprname = '?|';
The above yields:
oprname function----------------------------------------------------------------------------------?| point_vert(point, point)?| lseg_vertical(-, lseg)?| line_vertical(-, line)?| jsonb_exists_any(jsonb, text[]) <--- this is the one we're looking for?| exists_any(hstore, text[])
So, the simplest workaround is to just not use the operator, but the corresponding function instead:
try (PreparedStatement s = c.prepareStatement( "select jsonb_exists_any('{}'::jsonb, array['a', 'b']"); ResultSet rs = s.executeQuery()) { ...}