kotlin - Jooq fetchInto with default value if field is null

Question

I have data class

data class Author(
    val id: String,
    val name: String,
    val books: MutableList<Book> = mutableListOf()
) {}

And I wrote request using jooq

val resultSet = dsl.select(author.ID, author.NAME,
            field(select(jsonArrayAgg(jsonObject(book.ID, book.PRICE)))
                .from(books)
                .where(book.AUTHOR_ID.eq(author.ID))
            ).`as`("books"))
            .from(authors)
            .fetchInto(Author::class.java)

But I get an exception if Author has no books. It is logical, because field books in data class Author is not nullable. I tried to add companion object to data class

companion object {
        operator fun invoke(
            id: String,
            name: String,
            books: MutableList<Book>? = null
        ) = Author(id, name, books ?: mutableListOf())
    }

But it doesn't work, jooq still tries to use default constructor and I get an exception. Is there a way to fix it without making field books in data class Author nullable?

Answer 1

The reason is that JSON_ARRAYAGG() (like most aggregate functions) produces NULL for empty sets, instead of a more "reasonable" empty []. Clearly, you never want this behaviour. So, you could use COALESCE, instead, see also this question:

coalesce(
  jsonArrayAgg(jsonObject(book.ID, book.PRICE)),
  jsonArray()
)

I'll make sure to update all the other answers I've given on Stack Overflow to point this out. A future jOOQ version might offer NULL safe aggregate functions in case there exists a reasonable identity for aggregation (e.g. []) to make this more discoverable: https://github.com/jOOQ/jOOQ/issues/11994