Search code examples
kotlinjooqr2dbc

How to check JOOQ array type field is empty

Given there is Postgres array type, how to assemble where condition to check if it is empty.

I have tried to use the following clause, it did not work as expected.

 where = if (arrayExists== true) {
    where.and(DSL.length(USERS.ACCOUNT_IDS.name).greaterThan(0))
 } else {
    where.and(DSL.length(USERS.ACCOUNT_IDS.name).equal(0))
 }

NOTE: arrayExists is an external parameters from outside.

Solution

  • Out of the box, jOOQ supports the CARDINALITY() function via DSL.cardinality(Field<? extends Object[]>). It's what you'd write in PostgreSQL and standard SQL as well:

    select cardinality(array[1, 2])

    Producing

    |cardinality|
|-----------|
|2          |