java - How do I use PostgreSQL JSON(B) operators containing a question mark "?" via JDBC

Question

PostgreSQL knows a couple of funky ASCII-art operators that use the question mark character in their names, for instance these JSON operators:

• ? does the string exist as a top-level key within the JSON value?
• ?| Do any of these array strings exist as top-level keys?
• ?& Do all of these array strings exist as top-level keys?

The problem is that the official PostgreSQL JDBC driver does not seem to correctly parse SQL strings containing such operators. It assumes that the question mark is an ordinary JDBC bind variable. The following code...

try (PreparedStatement s = c.prepareStatement("select '{}'::jsonb ?| array['a', 'b']");
     ResultSet rs = s.executeQuery()) {
     ...
}

... throws an exception:

org.postgresql.util.PSQLException: Für den Parameter 1 wurde kein Wert angegeben.
    at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:225)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:190)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:424)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:161)
    at org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)

How can I use this operator?

Answer 1

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 function
FROM 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()) {
     ...
}