CODEWITHSUNDEEP
sqljooq
Michael Michailidis
Michael Michailidis

Reputation: 1052

Jooq ArrayAgg#isNull not working as expected

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 :

any clue what am I doing wrong?

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

Upvotes: 1

Views: 412

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 221145

{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());

Upvotes: 1

Related Questions