Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15

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"

(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo