How to use Postgres JSON operators in a WHERE condition with jOOQ?

Question

I have a JSONB column which holds arrays of strings, eg: ["foo", "bar"]

I want to write the jOOQ equivalent of:

SELECT * FROM sometable WHERE somecolumn ?| <mylist>

...where should be bound to a java List of string tag names.

There doesn't appear to be any direct support for ?| in jOOQ 3.8. I have looked at binding to raw sql in a condition but I'm not quite sure the syntax; it gets even worse if trying to use the ? postgres operator which conflicts with the binding expression.

UPDATE: the stacktrace with 3.8.3

I stripped this down to a minimal test. When adding a condition like this using jOOQ 3.8.3:

query.addConditions(DSL.condition("sometable.tags ?| array['sometag']"));

Produces a stacktrace like this:

Caused by: org.postgresql.util.PSQLException: No value specified for parameter 1.
    at org.postgresql.core.v3.SimpleParameterList.checkAllParametersSet(SimpleParameterList.java:228)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:163)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:622)
    at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:472)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:465)
    at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:194)
    at org.jooq.impl.AbstractResultQuery.execute(AbstractResultQuery.java:269)
    at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:348)
    ... 36 more

Answer 1

An issue related to jOOQ parsing ?| and similar operators as bind variables has been addressed in jOOQ 3.8.3: https://github.com/jOOQ/jOOQ/issues/5307

JDBC limitation

Note, in addition to the above, there is also a JDBC limitation that I have documented in a separate question. In jOOQ, you can work around this JDBC limitation by specifying:

Settings settings = new Settings().withStatementType(StatementType.STATIC_STATEMENT);

See also: http://www.jooq.org/doc/latest/manual/sql-execution/statement-type

Or, alternatively, by falling back to using the jsonb_exists_any() function instead of the ?| operator.