The mighty SQL/JSON and SQL/XML support

Starting with jOOQ 3.14, we have support for mapping a result set to any kind of hierarchical/nested structure that can be shaped via JSON or XML into, practically, almost anything. For instance, if you develop a REST API, you can return JSON/XML data in the exact desired shape without mapping anything to your domain model.

As you probably know, most RDBMSs support SQL/JSON (standard or vendor-specific), and some of them support SQL/XML, too.

Handling SQL/JSON support

In a nutshell, for SQL/JSON, we can talk about the following operators that have a jOOQ implementation in the org.jooq.impl.DSL class:

• JSON_OBJECT (DSL.jsonObject(), DSL.jsonEntry()), JSON_ARRAY (DSL.jsonArray()), and JSON_VALUE (DSL.jsonValue()) to construct JSON data from values
• JSON_ARRAYAGG (DSL.jsonArrayAgg()) and JSON_OBJECTAGG (DSL.jsonObjectAgg()) to aggregate data into nested JSON documents
• JSON_EXISTS (DSL.jsonExists()) to query documents with...