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

Community · Accepted Answer · 2017-05-23 12:02:03Z

3

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.

edited May 23, 2017 at 12:02

CommunityBot

11 silver badge

answered Jul 13, 2016 at 5:46

Lukas Eder

223k138 gold badges729 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

stickfigure Over a year ago

Thanks -- however, this doesn't seem to work with 3.8.3. Maybe because i'm using SelectQuery.addCondition()? Something seems to be passing a bind variable down to the postgres driver. I've updated the question with the relevant error message and stacktrace.

Lukas Eder Over a year ago

Thanks for the hint. I've indeed only fixed jOOQ's parser in order not to inline any bind variables at the wrong positions. There's also a JDBC bug / limitation, which I've documented in a separate question

Collectives™ on Stack Overflow

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

1 Answer 1

JDBC limitation

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

JDBC limitation

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related