Use UNNEST inside JOOQ's select

Question

I have the SQL like:

select *
from some_table
where (select array(select unnest(first_array_field) except select unnest(second_array_field))) @> '{some_value}';

This is my try to write the where condition for that query in a JOOQ way:

  private Condition selectAwayValue(
      List<Long> list) {

    var allSelect = dslContext
        .select(TABLE.ARRAY_1)
        .from(DSL.unnest(TABLE.ARRAY_1));

    var homeSelect = dslContext
        .select(TABLE.ARRAY_2)
        .from(DSL.unnest(TABLE.ARRAY_2));

    var awaySelect = allSelect.except(homeSelect);

    var awayArray = dslContext.select(DSL.array(awaySelect));

    return awayArray.asField().contains(awayCompetitorIdsList);
  }

I'm facing 2 problems here:

1. I got the different SQL than I expected (see above)
2. Also got the exception like Cannot convert from 5 (class java.lang.Long) to class [Ljava.lang.Long;

I understand what's the exceptions says to me, but don't understand why :)

What's would be the best way to write my SQL in a jooq syntax?

I'm using the latest stable jooq version, the latest stable Postgres version

thanks

Answer 1

Alternative solution

If you think of your problem this way:

select *
from some_table
where first_array_field @> '{some_value}'
and not second_array_field @> '{some_value}';

Then, the problem is a lot simpler to express in jOOQ as well

ctx.selectFrom(SOME_TABLE)
   .where(SOME_TABLE.FIRST_ARRAY_FIELD.contains(new Long[] { someValue }))
   .andNot(SOME_TABLE.SECOND_ARRAY_FIELD.contains(new Long[] { someValue }))
   .fetch();

Why your solution didn't work

Of course, you could pull this off using your own approach. Regarding the problems you've observed:

I got the different SQL than I expected (see above)

That syntax where you put UNNEST() in SELECT is not supported by jOOQ. It is quite the weird syntax in PostgreSQL as well, and doesn't do what you might think it does in many ways. Have you tried putting two instances of UNNEST() in SELECT? The second one won't form a cartesian product like the first one. Rows from it will be matched with the first one's rows by ordinality.

However, using UNNEST in FROM is correct and also more intuitive in native PostgreSQL, even if it's a bit longer. And it's supported by jOOQ.

Also got the exception like Cannot convert from 5 (class java.lang.Long) to class [Ljava.lang.Long;

This is because you seem to be calling Field.contains() with a scalar argument, instead of an array. See the example from the Javadoc, or my example above:

 // Use this expression
 val(new Integer[] { 1, 2, 3 }).contains(new Integer[] { 1, 2 })

 // ... to render this SQL
 ARRAY[1, 2, 3] @> ARRAY[1, 2]

But again, I think my alternative approach is much simpler.