Search code examples
postgresqljooq

JOOQ EXISTS clause with plain SQL

I'm building the WHERE clause of a JOOQ query. Some conditions use EXISTS, and I'd like to provide the sub-query as plain SQL, because they are long-winded expressed with JOOQ. eg.

select 1 from jsonb_array_elements(resource->'name') as t1,
  jsonb_array_elements_text(t1.value->'given') as t2
where t2.value like ?

However DSL.exists() accepts only Select type as parameter, and no matter which DSL.select...() method I use, it will wrap my SQL snippet with another select.

DSL.exists(DSL.selectFrom(DSL.sql(...)))

And the query will be like:

... where exists (select * from (select 1 from ...

Is there a way to avoid the unneeded wrapper select?

Solution

  • You could use a plain SQL condition like this:

    Condition condition = DSL.condition("exists (<your select here>)");