Jooq fetchInto with default value if field is null
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?
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