How to create a Union query with multiset fields in JOOQ

Question

I encountered an issue when using multiset in a unionAll query clause.

I am using the latest jOOQ 3.18.7/R2dbc/Postgres and Spring Boot 3.1.4, and Java 17/Kotlin1.9/Kotlin Coroutines 1.7.x.

The jOOQ query is similar to the following:

val fieldType = "type"
val fieldCreatedAt = "created_at"
val fieldAmount = "amount"
val fieldStatus = "status"
val fieldReportsIds ="reportIds"

val unionAllTable = select(
    TABLE_A.TYPE.`as`(fieldType),
    TABLE_A.CREATED_AT.`as`(fieldCreatedAt),
    TABLE_A.AMOUNT.`as`(fieldAmount),
    inline("COMPLETED").`as`(fieldStatus),
    DSL.multiset(
        ... // a subquery here, return a multiple UUID types
    ).`as`(fieldReportIds)
)
    .from(TABLE_A)
    .where(...)
    .unionAll(
        select(
            inline("TABLE_B").`as`(fieldType),
            TABLE_B.CREATED_AT.`as`(fieldCreatedAt),
            TABLE_B.AMOUNT.`as`(fieldAmount),
            TABLE_B.STATUS.`as`(fieldStatus),
            inline(DSL.any()).`as`(fieldReportIds) // how to set default empty value in the second table.
        )
            .from(TABLE_B)
            .where(...)
    )

val data = Flux
    .from(
        dslContext
            .select(
                field(fieldType, SQLDataType.VARCHAR),
                field(fieldCreatedAt, SQLDataType.LOCALDATETIME),
                field(fieldAmount, SQLDataType.DECIMAL),
                field(fieldStatus, SQLDataType.VARCHAR),
                field(fieldReportIds, SQLDataType.UUID.recordType) // how to set data type here.
            )
            .from(unionAllTable)
            .orderBy(field(fieldCreatedAt).desc())
            .limit(offset, limit)
    )
    .asFlow()
    //...

My question is:

• How to set default empty or null value for the multiset field in the second table?
• How to set the data type in the final query?

Answer 1

There's a pending feature request to support creating empty MULTISET expressions:

• https://github.com/jOOQ/jOOQ/issues/13152

Which is a special case of the standard SQL MULTISET by enumeration expression:

• https://github.com/jOOQ/jOOQ/issues/12032

As a workaround, just create an empty select with the appropriate row type:

multiset(
    select(inline(null, type1), inline(null, type2), ...)
    .where(falseCondition()))