Jooq ArrayAgg#isNull not working as expected

Question

I have a project which uses jooq + postgres with multiple tables and relations between them.

while I was creating a select query with jooq I had to use arrayAgg for my specific scenario.

dslContext.select(arrayAgg(tableName.INTEGER_LETS_SAY).as("static_name")

the specific column INTEGER_LETS_SAY is nullable. when the results passed in arrayAgg are all null then the response of the postgres is '{null}' ( tested with getQuery().getSql() ) but the where statement cannot return true for all the methods I tried.

for example :

• field("static_name", Long[].class).isNull()
• field("static_name", Long[].class).equal(new Long[] {null})
• field("static_name", Long[].class).equal(DSL.castNull(Long[].class)
• field("static_name", Long[].class).cast(String.class).eq(DSL.value("{null}")))
• field("static_name", Long[].class).cast(String.class).eq(DSL.value("'{null}'")))

any clue what am I doing wrong?

Note : I did try the query with plain sql and static_name = '{null}' worked

Answer 1

{NULL} is PostgreSQL's text representation of an array containing one SQL NULL value. You can try it like this:

select (array[null]::int[])::text ilike '{null}' as a

It yields:

a   |
----|
true|

Note, I'm using ilike for case insensitive comparison. On my installation, I'm getting {NULL}, not {null}. If you wanted to compare things as text, you could do it using Field.likeIgnoreCase(). E.g. this works for me:

System.out.println(ctx.select(
    field(val(new Long[] { null }).cast(String.class).likeIgnoreCase("{null}")).as("a")
).fetch());

Producing:

+----+
|a   |
+----+
|true|
+----+

But much better, do not work with the text representation. Instead, follow this suggestion here. In SQL:

select true = all(select a is null from unnest(array[null]::int[]) t (a)) as a

In jOOQ:

System.out.println(ctx.select(
    field(inline(true).eq(all(
        select(field(field(name("a")).isNull()))
        .from(unnest(val(new Long[] { null })).as("t", "a"))
    ))).as("a")
).fetch());

It gets a bit verbose because of all the wrapping Condition in Field<Boolean> using DSL.field(Condition).

Alternatively, use e.g. NUM_NONNULLS() (Credits to Vik Fearing for this appraoch):

System.out.println(ctx.select(
    field("num_nonnulls(variadic {0})", INTEGER, val(new Long { null }))
).fetch());