test JOOQ postgres jsonb column for key exists

Question

I've got a table TABLE that contains a jsonb column named tags. The tags element in each row may or may not contain a field called group. My goal is to group by tags.group for all rows where tags contains a group field. Like the following postgres query:

select tags->>'group' as group, sum(n) as sum
from TABLE
where tags ? 'group'
group by tags->>'group';

I'm trying to turn it into JOOQ and cannot find out how to express the where tags ? 'group' condition.

For example,

val selectGroup = DSL.field("{0}->>'{1}'", String::class.java, TABLE.TAGS, "group")
dsl().select(selectGroup, DSL.sum(TABLE.N))
  .from(TABLE)
  .where(TABLE.TAGS.contains('group'))
  .groupBy(selectGroup)

This is equivalent to testing contains condition @> in postgres. But I need to do exists condition ?. How can I express that in JOOQ?

Answer 1

There are two things worth mentioning here:

The ? operator in JDBC

Unfortunately, there's no good solution to this as ? is currently strictly limited to be used as a bind variable placeholder in the PostgreSQL JDBC driver. So, even if you could find a way to send that character to the server through jOOQ, the JDBC driver would still misinterpret it.

A workaround is documented in this Stack Overflow question.

Plain SQL and string literals

When you're using the plain SQL templating language in jOOQ, beware that there is a parser that will parse certain tokens of your string, including e.g. comments and string literals. This means that your usage of...

DSL.field("{0}->>'{1}'", String::class.java, TABLE.TAGS, "group")

is incorrect, as '{1}' will be parsed as a string literal and sent to the server as is. If you want to use a variable string literal, do this instead:

DSL.field("{0}->>{1}", String::class.java, TABLE.TAGS, DSL.inline("group"))

See also DSL.inline()