Jooq ARRAY_AGG where clause

Question

I have query which I want to execute using scala Jooq arguments.

select * from table where ("value1") in unnest(array) and ("value2") in unnest(array);

I can convert this part to below jooq arguments but i am not able to get what i need to do for where clause.

dslContext.select(asterisk()).from(table).where(*)

Answer 1

That particular syntax seems to be unsupported by jOOQ at the moment, see also this feature request: https://github.com/jOOQ/jOOQ/issues/12330

If you're ever missing a feature in jOOQ, you can always extend jOOQ via plain SQL templating. In this case:

def inUnnest[T](value: T, array: Field[Array[T]]): Condition = 
  inUnnest(DSL.value(value), array)

def inUnnest[T](value: Field[T], array: Field[Array[T]]): Condition =
  DSL.condition("{0} in unnest ({1})", value, array)