jOOQ 3.14 Released With SQL/XML and SQL/JSON Support

jOOQ 3.14 has been released with support for SQL/XML, SQL/JSON, Kotlin code generation, embeddable types, and domain types, synthetic constraints, better MERGE support, and more SQL transformations.

In this release, we’ve sorted our github issues according to user feedback and
finally implemented some of the most wanted features, which include better
Kotlin support, embeddable types, and domain type support.

In addition to this, we believe that our newly added XML and JSON operation
support will be a leading game changer in how ORMs interact with SQL databases
in the future.

XML and JSON

Standard SQL has long supported SQL/XML, and since recently, most RDBMS also
support standard SQL/JSON or vendor specific variants thereof. While ORDBMS
extensions have never seen the adoption they deserve, XML and JSON do. It makes
sense to occasionally denormalise data and store documents in the database
directly. However, this is not what we’re most excited about.

We’re excited about our support for all the fancy operators like:

  • JSON_TABLE to turn JSON values into SQL tables
  • JSON_ARRAY, JSON_OBJECT, JSON_VALUE to construct JSON data from values
  • JSON_ARRAYAGG, JSON_OBJECTAGG to aggregate data into nested JSON documents
  • JSON_EXISTS to query documents with JSON path

Similar functions are available for XML, like XMLTABLE, XMLELEMENT, XMLAGG, etc.
All editions of jOOQ 3.14 support standard SQL XML and JSON operators, and
emulate them where non-standard support is available (e.g. PostgreSQL and SQL
Server).

The commercial editions also support SQL Server’s very convenient FOR XML and
FOR JSON APIs, emulating that syntax using standard operators elsewhere. See:

https://blog.jooq.org/2020/05/05/using-sql-server-for-xml-and-for-json-syntax-on-other-rdbms-with-jooq/

But that’s not all of it! If you have Gson, Jackson, or JAXB on the classpath,
we can use that to map org.jooq.XML, org.jooq.JSON, org.jooq.JSONB types from
your query results to your nested data structures, automatically. See:

https://blog.jooq.org/2020/10/09/nesting-collections-with-jooq-3-14s-sql-xml-or-sql-json-support/

These approaches are extremely powerful. In many cases, you might just skip
most of your middleware layer and bind a REST or similar endpoint directly to a
jOOQ / SQL query, producing JSON for your frontend:

https://blog.jooq.org/2019/11/13/stop-mapping-stuff-in-your-middleware-use-sqls-xml-or-json-operators-instead/

Kotlin support

We’ve long supported some Scala extensions and a ScalaGenerator. Kotlin is an
additional very promising JVM language where a new jOOQ-kotlin module as well as
a KotlinGenerator will add a lot of value for your jOOQ/Kotlin integration.

The KotlinGenerator offers, among other things:

  • Data class support for generated POJOs
  • Property support for generated POJOs, interfaces and records
  • Better nullability support

The jOOQ-kotlin module offers some useful extension functions to further improve
the experience of the jOOQ/Kotlin integration.

In addition to the above, we’ve annotated the entire jOOQ API with nullability
annotations from JetBrains:

  • org.jetbrains.annotations.Nullable
  • org.jetbrains.annotations.NotNull

This will remove many of the annoying T! types in your Kotlin/jOOQ code,
turning them into T or T? types instead, giving you more confidence.

With these improvements, we’ve also critically reviewed our existing
ScalaGenerator, fixing a lot of bugs.

Embeddable types

One of the biggest new features in jOOQ 3.14 is inspired by JPA, which ships
with embeddable types. An embeddable type is an emulation of a database user-
defined type (UDT), which are supported natively only in Oracle and PostgreSQL.
The biggest gain of such types is to create more semantic, composite data types
in your database schema, and profit from the additional type safety.

Our interpretation of the feature is mostly in the source code generator, whose
output now becomes even more valuable. All jOOQ editions support the basic
infrastructure to pattern-match column sets and turn them into synthetic
embeddable types.

In addition to the above, our commercial editions offer some auto configuration
of embeddable types in cases where they really shine:

  • For primary/unique constraints and their matching foreign keys
  • For DOMAIN types (see below)
  • Handling overlapping embeddable types
  • Allowing for embeddable types to replace their underlying columns

We took the concept a step further than JPA. In jOOQ, embeddable types can act
as views on the underlying columns, without replacing them, or as a replacement
like in JPA. jOOQ respects all levels of relational modelling, including
overlapping constraints and thus allowing for two embeddable types to overlap.

For more information, please refer to:

https://www.jooq.org/doc/3.14/manual/code-generation/codegen-embeddable-types/

DOMAIN types

Speaking of types, some database dialects support standard SQL DOMAIN types,
which are a simpler form of UDTs. Instead of working with low level technical
types like VARCHAR(10), why not give your single-column types a name, and add
a few re-usable CHECK constraints to them?

That’s what a DOMAIN type is:

  • A named type
  • Aliasing a technical type, like VARCHAR(10)
  • Possibly adding a DEFAULT expression
  • Possibly adding a NOT NULL constraint
  • Possibly adding a set of CHECK constraints

All of the above is reusable across your schema and if you’re commercially
licensed, you can even have the code generator auto-generate embeddable types
for all of your domains to profit from the additional type safety in Java.

For more information, please refer to:

Synthetic constraints

Related to the above improved code generator output are synthetic objects,
such as the previously supported synthetic primary keys, and now also synthetic
unique and foreign keys.

If you invest heavily in security and re-usable components within your database,
you will make heavy use of SQL views. Unfortunately, views do not have any meta
data like foreign key constraints – despite the meta data being “obvious” to you
the database designer. With synthetic constraints, you can tell jOOQ about your
knowledge of underlying constraints.

The main benefits of meta data being available to jOOQ being:

  • You can now use implicit joins on views as well
  • You can now use the JOIN .. ON KEY syntax on views as well
  • You can use embeddable key types from before on views just like on tables

For more information, please refer to:

Better MERGE support

We’ve finally tackled support for more advanced MERGE statement clauses and now
support:

  • Multiple WHEN MATCHED AND condition THEN UPDATE clauses
  • Multiple WHEN MATCHED AND condition THEN DELETE clauses
  • UpdatableRecord.merge() and all the related goodies to simplify record merging

Transformations

With the parser and our translator tool (https://www.jooq.org/translate), we’ll
invest more and more in new use-cases for putting jOOQ to use other than as an
embeddable DSL in Java.

Our translation capabilities have already been strong, and with a new set of SQL
transformations, they become even stronger helping customers migrate off RDBMS A
towards RDBMS B (and back, if they made a mistake).

While our website translator is free of charge, the jOOQ library can always be
used programmatically, or as a command line utility:

https://www.jooq.org/doc/3.14/manual/sql-building/sql-parser/sql-parser-cli/

To make this use-case even more useful, new transformation features have been
added, including:

  • ROWNUM to LIMIT or to ROW_NUMBER()
  • Table lists to ANSI JOIN (including Oracle (+) support)
  • Unnecessary arithmetic expressions

This is an exciting area that we’ll be exploring for our commercially licensed
customers in the future, while even the jOOQ Open Source Edition profits from
these improvements. For example, the infrastructure created for transformations
finally enabled emulating PostgreSQL’s DISTINCT ON clause, elsewhere.

For more information, please refer to:

Better manual

We’ve taken a step back and reviewed a few important parts of our documentation.
We’re now offering:

  • Sticky tabs for code generator techniques (XML, Programmatic, Gradle): If
    you’re using Gradle with jOOQ’s code generator, you don’t want to look at the
    XML configuration again, in the manual. These tabs finally hide unneeded
    information.
  • Subsections for each function: We’ve started documenting each SQL function
    individually, explaining how it works in SQL, and providing some examples and
    example results.
  • Generate example vendor specific rendering of SQL: We’re using jOOQ when
    generating the manual, translating some jOOQ API usage to all of our supported
    dialects, and displaying how the function renders in each dialect.
  • Show imports button and display context sensitive imports: All the examples in
    the manual can be overwhelming. We’re assuming a lot of (static) imports,
    which we’re finally documenting in an expandable “show imports” section of
    each code fragment.
  • We’ve rewritten some sections to be much more complete with examples, such as
    the data import section.
  • A new API diff page displays what has changed between each minor release in
    terms of list of Javadoc links: https://www.jooq.org/api-diff

Using jOOQ 3.14 Synthetic Foreign Keys to Write Implicit Joins on Views

jOOQ has supported one of JPQL’s most cool features for a while now: implicit joins. Using jOOQ, you can navigate your to-one relationships in a type safe way, generating LEFT JOIN operations implicitly without the effort of having to keep thinking about join predicates, and the correct join order. Consider this Sakila database query here, if SQL supported implicit joins natively:

SELECT
  cu.first_name,
  cu.last_name
FROM customer AS cu
WHERE cu.address.city.country.country = 'Switzerland'

It translates to this query in native SQL:

SELECT
  cu.first_name,
  cu.last_name
FROM customer AS cu
JOIN address AS ad ON cu.address_id = ad.address_id
JOIN city AS ci ON a.city_id = ci.city_id
JOIN country AS co ON ci.country_id = co.country_id
WHERE co.country = 'Switzerland'

Note: Inner joins are supported starting from jOOQ 3.14, depending on whether the foreign key is mandatory / not null. The default behaviour is to produce LEFT JOIN which are the correct way to implicitly join optional foreign keys.

Implicit joins aren’t a silver bullet. Not every JOIN graph can be completely transformed into implicit join usage, and not every implicit join usage is more readable than native SQL JOIN graphs. But to have this option is great. Especially, when your keys are composite keys.

Classic joins on views

In classic relational design, surrogate keys are often avoided, and I think we should still avoid them in many cases. Even if you don’t agree, you may occasionally work on a schema where there are few to no surrogate keys. One such example is the standard SQL INFORMATION_SCHEMA which is implemented, for example, in H2, HSQLDB, MariaDB, MySQL, PostgreSQL, or SQL Server.

For example, when querying HSQLDB’s DOMAIN_CONSTRAINTS view to reverse engineer DOMAIN types. The jOOQ query for that used to be:

Domains d = DOMAINS.as("d");
DomainConstraints dc = DOMAIN_CONSTRAINTS.as("dc");
CheckConstraints cc = CHECK_CONSTRAINTS.as("cc");

for (Record record : create()
  .select(
    d.DOMAIN_SCHEMA,
    d.DOMAIN_NAME,
    d.DATA_TYPE,
    d.CHARACTER_MAXIMUM_LENGTH,
    d.NUMERIC_PRECISION,
    d.NUMERIC_SCALE,
    d.DOMAIN_DEFAULT,
    cc.CHECK_CLAUSE)
  .from(d)
    .join(dc)
      .on(row(d.DOMAIN_CATALOG, d.DOMAIN_SCHEMA, d.DOMAIN_NAME)
          .eq(dc.DOMAIN_CATALOG, dc.DOMAIN_SCHEMA, dc.DOMAIN_NAME))
    .join(cc)
      .on(row(dc.CONSTRAINT_CATALOG, 
              dc.CONSTRAINT_SCHEMA, 
              dc.CONSTRAINT_NAME)
          .eq(cc.CONSTRAINT_CATALOG, 
              cc.CONSTRAINT_SCHEMA, 
              cc.CONSTRAINT_NAME))
  .where(d.DOMAIN_SCHEMA.in(getInputSchemata()))
  .orderBy(d.DOMAIN_SCHEMA, d.DOMAIN_NAME)
) { ... }

So, the query joined the many-to-many relationship between DOMAINS - DOMAIN_CONSTRAINTS - CHECK_CONSTRAINTS to get all the information required for generating domain types.

These views are not updatable, nor do they have any constraint information associated with them, but what if we were able to define synthetic constraints? jOOQ has supported synthetic primary keys to help make views updatable.

Synthetic foreign keys

Starting with jOOQ 3.14, we’ve reworked the way synthetic keys work, and the commercial editions will support synthetic foreign keys as well.

You can specify a configuration like this:

<configuration>
  <generator>
    <database>
      <syntheticObjects>
        <primaryKeys>
          <primaryKey>
            <tables>
              CHECK_CONSTRAINTS|CONSTRAINTS|TABLE_CONSTRAINTS
            </tables>
            <fields>
              <field>CONSTRAINT_(CATALOG|SCHEMA|NAME)</field>
            </fields>
          </primaryKey>
          <primaryKey>
            <tables>DOMAINS</tables>
            <fields>
              <field>DOMAIN_(CATALOG|SCHEMA|NAME)</field>
            </fields>
          </primaryKey>    
        </primaryKeys>  
        <foreignKeys>
          <foreignKey>
            <tables>DOMAIN_CONSTRAINTS</tables>
            <fields>
              <field>CONSTRAINT_(CATALOG|SCHEMA|NAME)</field>
            </fields>
            <referencedTable>CHECK_CONSTRAINTS</referencedTable>
          </foreignKey>
          <foreignKey>
            <tables>DOMAIN_CONSTRAINTS</tables>
            <fields>
              <field>DOMAIN_(CATALOG|SCHEMA|NAME)</field>
            </fields>
            <referencedTable>DOMAINS</referencedTable>
          </foreignKey>
        </foreignKeys>
      </syntheticObjects>
    </database>
  </generator>
</configuration>

And already jOOQ’s code generator will think that these views were tables that actually had constraints like the below:

ALTER TABLE CHECK_CONSTRAINTS 
  ADD PRIMARY KEY (
    CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME
  );

ALTER TABLE DOMAINS 
  ADD PRIMARY KEY (
    DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME
  );

ALTER TABLE DOMAIN_CONSTRAINTS
  ADD FOREIGN KEY (
    CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME
  )
  REFERENCES CHECK_CONSTRAINTS;

ALTER TABLE DOMAIN_CONSTRAINTS
  ADD FOREIGN KEY (
    DOMAIN_CATALOG, DOMAIN_SCHEMA, DOMAIN_NAME
  )
  REFERENCES DOMAINS;

More sophisticated configuration is possible, e.g. to assign names to constraints, to have composite constraints using field ordering differing from the ordering in the table, or foreign keys referencing unique keys rather than primary keys. For a full description of what’s available, please refer to the manual.

With the above synthetic meta data available to the code generator, all the numerous goodies are now available on views as well, including:

Let’s look at

Implicit joins

Implicit joins are now also possible on these views, meaning:

  1. You’ll never have to remember including all the key columns in join predicates anymore (bye bye accidental cartesian products)
  2. Your code will still be correct in case your composite key changes to something else!

So, this is more than just merely a convenience thing, it’s also a correctness thing. Our query from before can now be written like this, in a much more concise way:

DomainConstraints dc = DOMAIN_CONSTRAINTS.as("dc");

for (Record record : create()
    .select(
        dc.domains().DOMAIN_SCHEMA,
        dc.domains().DOMAIN_NAME,
        dc.domains().DATA_TYPE,
        dc.domains().CHARACTER_MAXIMUM_LENGTH,
        dc.domains().NUMERIC_PRECISION,
        dc.domains().NUMERIC_SCALE,
        dc.domains().DOMAIN_DEFAULT,
        dc.checkConstraints().CHECK_CLAUSE)
    .from(dc)
    .where(dc.domains().DOMAIN_SCHEMA.in(getInputSchemata()))
    .orderBy(dc.domains().DOMAIN_SCHEMA, dc.domains().DOMAIN_NAME)
) { ... }

Notice how we’re using the relationship table as the only table to put in the FROM clause. This way, we can navigate in both directions of the to-one relationships from DOMAIN_CONSTRAINTS -> DOMAINS and DOMAIN_CONSTRAINTS -> CHECK_CONSTRAINTS. The resulting SQL query is equivalent to the previous one, but all the nastiness of joining by 3-column-composite-keys is gone. I personally find this much more readable.

Future work

So far, only to-one relationships can be navigated this way. JPQL also offers navigating to-many relationships with a few restrictions. This is a slippery slope. When offering to-many relationships, some use-cases are obvious, but the semantics of others is less so. For example, it is not a good idea to let the SELECT clause produce more (or less) rows depending on the presence of a projected column. This is why jOOQ so far produced only LEFT JOIN for implicit joins, because that would guarantee that an implicitly joined column does not reduce the number of rows because of an INNER JOIN not producing any matches.

Nevertheless, there is a lot that we can still add in #7536, including:

  • Implicit to-many joins in the FROM clause, where they don’t cause any trouble
  • Implicit joins in DML
  • Parser support to offer this functionality also in https://www.jooq.org/translate and to everyone working with jOOQ SQL through jOOQ’s ParsingConnection

And much more!

Nesting Collections With jOOQ 3.14’s SQL/XML or SQL/JSON support

One of the main features of ORMs is M as in Mapping. Libraries like jOOQ help auto-mapping flat or nested database records onto Java classes that have the same structure as the SQL result set.

The following has always been possible in jOOQ, assuming PostgreSQL’s INFORMATION_SCHEMA (using the generated code from the jOOQ-meta module):

class Column {
    String tableSchema;
    String tableName;
    String columnName;
}

for (Column c :
    ctx.select(
            COLUMNS.TABLE_SCHEMA, 
            COLUMNS.TABLE_NAME, 
            COLUMNS.COLUMN_NAME)
       .from(COLUMNS)
       .where(COLUMNS.TABLE_NAME.eq("t_author"))
       .orderBy(COLUMNS.ORDINAL_POSITION)
       .fetchInto(Column.class))
    System.out.println(
        c.tableSchema + "." + c.tableName + "." + c.columnName
    );

The above resulting in something like:

public.t_author.id
public.t_author.first_name
public.t_author.last_name
public.t_author.date_of_birth
public.t_author.year_of_birth
public.t_author.address

The mapping is straight forward, as explained in jOOQ’s DefaultRecordMapper.

Nested mappings

A lesser known feature that we’ve offered for a while was to use a dot notation to emulate nesting records into nested Java classes. Assuming you want to use a re-usable data type description in your columns and elsewhere:

class Type {
    String name;
    int precision;
    int scale;
    int length;
}

class Column {
    String tableSchema;
    String tableName;
    String columnName;
    Type type;
}

You can now write this query where you’ll alias some columns using the dot notation to type.name, for example (several nesting levels are possible):

for (Column c :
    ctx.select(
            COLUMNS.TABLE_SCHEMA,
            COLUMNS.TABLE_NAME,
            COLUMNS.COLUMN_NAME,
            COLUMNS.DATA_TYPE.as("type.name"),
            COLUMNS.NUMERIC_PRECISION.as("type.precision"),
            COLUMNS.NUMERIC_SCALE.as("type.scale"),
            COLUMNS.CHARACTER_MAXIMUM_LENGTH.as("type.length")
       )
       .from(COLUMNS)
       .where(COLUMNS.TABLE_NAME.eq("t_author"))
       .orderBy(COLUMNS.ORDINAL_POSITION)
       .fetchInto(Column.class))

    System.out.println(String.format(
        "%1$-30s: %2$s",
        c.tableSchema + "." + c.tableName + "." + c.columnName,
        c.type.name + (c.type.precision != 0
               ? "(" + c.type.precision + ", " + c.type.scale + ")"
               :       c.type.length != 0
               ? "(" + c.type.length + ")"
               : "")
    ));

The above will print:

public.t_author.id            : integer(32, 0)
public.t_author.first_name    : character varying(50)
public.t_author.last_name     : character varying(50)
public.t_author.date_of_birth : date
public.t_author.year_of_birth : integer(32, 0)
public.t_author.address       : USER-DEFINED

Using XML or JSON

Using XML or JSON, starting from jOOQ 3.14, you can also nest collections in your result set mapping very easily. First, let’s look again at how to use a JSON query using jOOQ, e.g. to find all columns per table:

for (Record1<JSON> record :
    ctx.select(
            jsonObject(
                jsonEntry("tableSchema", COLUMNS.TABLE_SCHEMA),
                jsonEntry("tableName", COLUMNS.TABLE_NAME),
                jsonEntry("columns", jsonArrayAgg(
                    jsonObject(
                        jsonEntry("columnName", COLUMNS.COLUMN_NAME),
                        jsonEntry("type", jsonObject(
                            "name", COLUMNS.DATA_TYPE)
                        )
                    )
                ).orderBy(COLUMNS.ORDINAL_POSITION))
            )
       )
       .from(COLUMNS)
       .where(COLUMNS.TABLE_NAME.in("t_author", "t_book"))
       .groupBy(COLUMNS.TABLE_SCHEMA, COLUMNS.TABLE_NAME)
       .orderBy(COLUMNS.TABLE_SCHEMA, COLUMNS.TABLE_NAME)
       .fetch())
    System.out.println(record.value1());

The following JSON documents are returned:

{
  "tableSchema": "public", 
  "tableName": "t_author", 
  "columns": [{
    "columnName": "id", 
    "type": {"name": "integer"}
  }, {
    "columnName": "first_name", 
    "type": {"name": "character varying"}
  }, {...}]
}

{
  "tableSchema": "public", 
  "tableName": "t_book", 
  "columns": [{...}, ...]
}

That’s already awesome, isn’t it? We’ve blogged about this previously here and here. Starting with jOOQ 3.14, you can remove all the other middleware and mapping and what not, and produce your XML or JSON documents directly from your database using standard SQL/XML or SQL/JSON API!

But that’s not all!

Maybe, you don’t actually need the JSON document, you just want to use JSON to allow for nesting data structures, mapping them back to Java.

What about these nested Java classes:

public static class Type {
    public String name;
}

public static class Column {
    public String columnName;
    public Type type;
}

public static class Table {
    public String tableSchema;
    public String tableName;

    public List<Column> columns;
}

Assuming you have gson or Jackson or JAXB on your classpath (or you configure them directly), you can write the exact same query as before, and use jOOQ’s DefaultRecordMapper using the fetchInto(Table.class) call:

for (Table t :
    ctx.select(
            jsonObject(
                jsonEntry("tableSchema", COLUMNS.TABLE_SCHEMA),
                jsonEntry("tableName", COLUMNS.TABLE_NAME),
                jsonEntry("columns", jsonArrayAgg(
                    jsonObject(
                        jsonEntry("columnName", COLUMNS.COLUMN_NAME),
                        jsonEntry("type", jsonObject(
                            "name", COLUMNS.DATA_TYPE)
                        )
                    )
                ).orderBy(COLUMNS.ORDINAL_POSITION))
            )
       )
       .from(COLUMNS)
       .where(COLUMNS.TABLE_NAME.in("t_author", "t_book"))
       .groupBy(COLUMNS.TABLE_SCHEMA, COLUMNS.TABLE_NAME)
       .orderBy(COLUMNS.TABLE_SCHEMA, COLUMNS.TABLE_NAME)
       .fetchInto(Table.class))
    System.out.println(t.tableName + ":\n" + t.columns
       .stream()
       .map(c -> c.columnName + " (" + c.type.name + ")")
       .collect(joining("\n  ")));

The output being:

t_author:
  id (integer)
  first_name (character varying)
  last_name (character varying)
  date_of_birth (date)
  year_of_birth (integer)
  address (USER-DEFINED)
t_book:
  id (integer)
  author_id (integer)
  co_author_id (integer)
  details_id (integer)
  title (character varying)
  published_in (integer)
  language_id (integer)
  content_text (text)
  content_pdf (bytea)
  status (USER-DEFINED)
  rec_version (integer)
  rec_timestamp (timestamp without time zone)

No join magic. No cartesian products. No data deduplication. Just SQL-native nested collections, using an intuitive, declarative approach to creating the document data structure, combined with the usual awesomeness of SQL.

Using this without the jOOQ DSL

Of course, this also works without the jOOQ API, e.g. using our parser. Check out our translator tool. Plug in this native SQL beauty:

SELECT
  json_object(
    KEY 'tableSchema' VALUE columns.table_schema,
    KEY 'tableName' VALUE columns.table_name,
    KEY 'columns' VALUE json_arrayagg(
      json_object(
        KEY 'columnName' VALUE columns.column_name,
        KEY 'type' VALUE json_object(
          KEY 'name' VALUE columns.data_type
        )
      )
    )
  )
FROM columns
WHERE columns.table_name IN ('t_author', 't_book')
GROUP BY columns.table_schema, columns.table_name
ORDER BY columns.table_schema, columns.table_name

And, because the devil of SQL agnosticity and translation is in the detail, take out the vendor-specific version, e.g. for PostgreSQL:

SELECT json_build_object(
  'tableSchema', columns.table_schema,
  'tableName', columns.table_name,
  'columns', json_agg(json_build_object(
    'columnName', columns.column_name,
    'type', json_build_object('name', columns.data_type)
  ))
)
FROM columns
WHERE columns.table_name IN (
  't_author', 't_book'
)
GROUP BY
  columns.table_schema,
  columns.table_name
ORDER BY
  columns.table_schema,
  columns.table_name

You might need to run this, before:

SET search_path = 'information_schema'

Conclusion

We’ve waited way too long with this game changing feature. I truly think this approach will change how we perceive ORMs in the future. The database first approach, where we can use SQL and only SQL to map SQL data onto any hierarchical data structure is very commpelling.

On the jOOQ side, we’re far from done yet. What if we can auto-generate some of the JSON document declaration from other types of meta data for you? What if you can do that, yourself? E.g. to map a GraphQL specification to jOOQ API based JSON queries? On all the SQL dialects that support these features!

The future of mapping nested data structures from SQL to any client, XML, JSON, objects is bright. jOOQ 3.14 is around the corner and will be released within the next 2 weeks. You can already build it from github: https://github.com/jOOQ/jOOQ, or if you have a license, download a nightly build from here: https://www.jooq.org/download/versions

Looking forward to your feedback.