Jdbi: Escaping the jsonb question mark operator in postgres

[jimmyhmiller]

I'm trying to use jdbi with postgres and jsonb to write a query like this:

select id, data from some_table where data ? :key

Unfortunately, jdbi interprets this question mark as a placeholder for a variable. How can I escape the question mark so I can use the postgres question mark operator?

[stevenschlansker]

Have you tried the backslash \? ?

[jimmyhmiller]

Yeah, I guess I should have mentioned that. That was my first instinct. But I still get an error.

Exception in thread "main" org.skife.jdbi.v2.exceptions.UnableToExecuteStatementException: org.postgresql.util.PSQLException: No value specified for parameter 2. [statement:"select id, createdDate, data from something where data \? ?", located:"select id, createdDate, data from something where data \? ?", rewritten:"select id, createdDate, data from something where data ? ?", arguments:{ positional:{0:'pattern'}, named:{}, finder:[]}]
    at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1334)
    at org.skife.jdbi.v2.Query.fold(Query.java:173)
    at org.skife.jdbi.v2.Query.list(Query.java:82)
    at org.skife.jdbi.v2.Query.list(Query.java:75)
    at com.ngc.vault.eventer.Query.hasKey(Query.java:45)
    at com.ngc.vault.eventer.Main.main(Main.java:34)
Caused by: org.postgresql.util.PSQLException: No value specified for parameter 2.
    at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:228)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:163)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:615)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:465)
    at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:458)
    at org.skife.jdbi.v2.SQLStatement.internalExecute(SQLStatement.java:1328)
    ... 5 more

[jimmyhmiller]

Turns out this is an upstream problem in jdbc. They have implemented a way to escape it. You have to used ??. Like so:

select id, data from some_table where data ?? :key

[pritam12140]

Postgres query
SELECT json_data FROM employee where json_data -> 'employee' @> '{"name":"Aman"}'

The above query works fine when run in postgres. But when run with jdbctemplate, it throws an error.

Java Code

String sql="SELECT json_data FROM employee where json_data -> 'employee' @> '{\"name\":\"?\"}'";

List<Map<String, Object>> emp = jdbcTemplate.queryForList(sql,param);

On encountering the last line of the code,it throws an error:- The column index is out of range: 1, number of columns: 0.; nested exception is org.postgresql.util.PSQLException.

It is not able to substitute the '?' placeholder.

[qualidafial]

That is according to the JDBC spec. ? markers are not recognized as parameters inside string literals.

If you need to interpolate a parameter into that JSON string, use concatenation. Using Postgres concatenation syntax, the query would look like:

SELECT json_data
FROM employee
WHERE json_data -> 'employee' @> ('{"name": "' || ? || '"}')

[qualidafial]

Please note also that this is the Jdbi project, not JdbcTemplate. :)