Nested collections via the astonishing MULTISET
The MULTISET value constructor (or MULTISET for short) is a SQL standard future that shapes nested subqueries (except scalar subqueries) into a single nested collection value. jOOQ 3.15+ provides marvelous and glorious support for MULTISET. It's marvelous because despite its tremendous power, it is quite easy (effortless) and intuitive to use via jOOQ, and it is glorious because it can produce any nested collection value of jOOQ Record or DTO (POJO/Java records) in a fully type-safe manner, with 0 reflections, no N+1 risks, no deduplications. This allows the database to perform nesting and to optimize the query execution plan.
Consider the well-known one-to-many relationship between PRODUCTLINE and PRODUCT. We can fetch and map this relationship via jOOQ's <R extends Record> Field<Result<R>> multiset(Select<R> select), in jOOQ before 3.17.x, and Field<Result<R>> multiset(TableLike<...
