Nested records
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The DSL.row() constructor isn't only useful for different types of row value expression predicates, but also to project nested record types, in most cases even Record1 to Record22 types, which maintain column level type safety.
All org.jooq.Row1 to org.jooq.Row22 types as well as the org.jooq.RowN type extend org.jooq.SelectField, meaning they can be placed in the SELECT clause or the RETURNING clause. The T type variable in SelectField<T> is bound to the appropriate Record1 to Record22 type, which allows for easily projecting nested records:
SELECT
ID,
ROW(
FIRST_NAME,
LAST_NAME
)
FROM AUTHOR
// Type inference via lambdas or var really shines here!
Result<Record2<Integer, Record2<String, String>>> result =
create.select(
AUTHOR.ID,
row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME))
.from(AUTHOR)
.fetch();
Combining nested records with arrays
If your RDBMS supports ARRAY types and ARRAY constructors, and if nested records are natively supported, chances are that you can combine the two features. For example, to find all books for an author, as a nested collection rather than a flat join:
SELECT
ID,
ROW(
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME
),
ARRAY(
SELECT BOOK.ID, BOOK.TITLE
FROM BOOK
WHERE BOOK.AUTHOR_ID = AUTHOR.ID
)
FROM AUTHOR
// Type inference via lambdas or var really shines here!
Result<Record3<
Integer,
Record2<String, String>,
Record2<Integer, String>[]
>> result =
create.select(
AUTHOR.ID,
row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)
array(
select(row(BOOK.ID, BOOK.TITLE))
.from(BOOK)
.where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
)
)
.from(AUTHOR)
.fetch();
Attaching RecordMappers to nested records
Nested records help structure your result sets using structural typing, but they really shine when you attach a RecordMapper to them. A RecordMapper is a java.lang.FunctionalInterface that can convert a Record subtype to any user type E. By calling e.g. Row2.mapping(), you can attach an ad-hoc converter to the nested record type to turn the nested object into something much more meaningful:
// Especially useful using Java 16 record types!
record Name(String firstName, String lastName) {}
record Author(int id, Name name) {}
// The "scary" structural type has gone!
List<Author> authors =
create.select(
AUTHOR.ID,
row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).mapping(Name::new)
)
.from(AUTHOR)
.fetch(Records.mapping(Author::new));
All of the above is type safe and uses no reflection! Try it out yourself - add or remove a column to the query or to the records, and observe the compilation errors that appear.
Now for the ARRAY example:
record Name(String firstName, String lastName) {}
record Book(int id, String title) {}
record Author(int id, Name name, Book[] books) {}
// Again, no structural typing here has gone!
List<Author> authors =
create.select(
AUTHOR.ID,
row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).mapping(Name::new),
array(
select(row(BOOK.ID, BOOK.TITLE).mapping(Book.class, Book::new)
.from(BOOK)
.where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
)
)
.from(AUTHOR)
.fetch(Records.mapping(Author::new));
Again, everything is type safe. Unfortunately, reflection is needed in this case to construct a Book[] array. You must pass the Book.class reference to help jOOQ with that. If you prefer lists, no problem. You can wrap the array again using the same technique, using an explicit ad-hoc converter:
record Name(String firstName, String lastName) {}
record Book(int id, String title) {}
record Author(int id, Name name, List<Book> books) {} // Is now using a List<Book> instead of Book[]
// Again, no structural typing here has gone!
List<Author> authors =
create.select(
AUTHOR.ID,
row(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME).mapping(Name::new),
array(
select(row(BOOK.ID, BOOK.TITLE).mapping(Book.class, Book::new)
.from(BOOK)
.where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
).convertFrom(Arrays::asList) // Additional converter here
)
.from(AUTHOR)
.fetch(Records.mapping(Author::new));
Dialect support
This example using jOOQ:
select(row(BOOK.ID, BOOK.TITLE))
Translates to the following dialect specific expressions:
Access
SELECT BOOK.ID nested__ID, BOOK.TITLE nested__TITLE FROM ( SELECT count(*) dual FROM MSysResources ) AS dual
ASE, BigQuery, Exasol, H2, MariaDB, MySQL, Oracle, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Trino, Vertica
SELECT BOOK.ID nested__ID, BOOK.TITLE nested__TITLE
Aurora MySQL, MemSQL
SELECT BOOK.ID nested__ID, BOOK.TITLE nested__TITLE FROM DUAL
Aurora Postgres, CockroachDB, DuckDB, Postgres, YugabyteDB
SELECT ROW (BOOK.ID, BOOK.TITLE) nested
ClickHouse
SELECT TUPLE (BOOK.ID, BOOK.TITLE) nested
DB2
SELECT BOOK.ID nested__ID, BOOK.TITLE nested__TITLE FROM SYSIBM.DUAL
Derby
SELECT BOOK.ID nested__ID, BOOK.TITLE nested__TITLE FROM SYSIBM.SYSDUMMY1
Firebird
SELECT BOOK.ID nested__ID, BOOK.TITLE nested__TITLE FROM RDB$DATABASE
Hana, Sybase
SELECT BOOK.ID nested__ID, BOOK.TITLE nested__TITLE FROM SYS.DUMMY
HSQLDB
SELECT BOOK.ID nested__ID, BOOK.TITLE nested__TITLE FROM (VALUES (1)) AS dual (dual)
Informix
SELECT ROW (BOOK.ID, BOOK.TITLE) nested FROM ( SELECT 1 AS dual FROM systables WHERE (tabid = 1) ) AS dual
Teradata
SELECT BOOK.ID nested__ID, BOOK.TITLE nested__TITLE FROM ( SELECT 1 AS "dual" ) AS "dual"
Generated with jOOQ 3.20. Translate your own SQL on our website
References to this page
Feedback
Do you have any feedback about this page? We'd love to hear it!