JOOQ EXISTS clause with plain SQL

user_895691 0
Viewed 546

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?

jooq postgresql
3 Answers

Why do you put the SQL in DSL.sql?

You can pass the SQL string directly to selectFrom:

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

Then you don't get this wrapper select.

user_10451420

For now all I could do was to chop the SQL to clauses. I would still be interested in a different solution.

return DSL.exists(DSL.selectOne().from(
    "jsonb_array_elements(resource->'name') as t1," +
    " jsonb_array_elements_text(t1.value->'given') as t2"
).where(
    "t2.value like ?", pattern)
);
user_8956910

You could use a plain SQL condition like this:

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