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

MULTISET value constructor

Applies to ✅ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

The MULTISET value constructor is one of jOOQ's and standard SQL's most powerful features. It allows for collecting the results of a non scalar subquery into a single nested collection value with MULTISET semantics (ordinals are not defined on elements, though jOOQ attempts to maintain ORDER BY produced ordering when projecting a MULTISET).

For example, let's find:

  • All authors.
  • The languages in which that author has their books published.
  • The book stores at which that author's books are available.

This can be done in a single query:

SELECT
  AUTHOR.FIRST_NAME,
  AUTHOR.LAST_NAME,
  MULTISET(
    SELECT DISTINCT
      LANGUAGE.CD
      LANGUAGE.DESCRIPTION
    FROM BOOK
    JOIN LANGUAGE ON BOOK.LANGUAGE_ID = LANGUAGE.ID
    WHERE BOOK.AUTHOR_ID = AUTHOR.ID
  ) AS BOOKS,
  MULTISET(
    SELECT DISTINCT BOOK_TO_BOOK_STORE.BOOK_STORE_NAME
    FROM BOOK_TO_BOOK_STORE
    JOIN BOOK ON BOOK_TO_BOOK_STORE.BOOK_ID = BOOK.ID
    WHERE BOOK.AUTHOR_ID = AUTHOR.ID
  ) AS BOOK_STORES
FROM AUTHOR
ORDER BY AUTHOR.ID
var result = create.select(
          AUTHOR.FIRST_NAME,
          AUTHOR.LAST_NAME,
          multiset(
              selectDistinct(
                  BOOK.language().CD,
                  BOOK.language().DESCRIPTION)
              .from(BOOK)
              .where(BOOK.AUTHOR_ID.eq(AUTHOR.ID))
          ).as("books"),
          multiset(
              selectDistinct(BOOK_TO_BOOK_STORE.BOOK_STORE_NAME)
              .from(BOOK_TO_BOOK_STORE)
              .where(BOOK_TO_BOOK_STORE.tBook().AUTHOR_ID.eq(AUTHOR.ID))
          ).as("book_stores"))
      .from(AUTHOR)
      .orderBy(AUTHOR.ID)
      .fetch();

Notice how the Java 10 var keyword really shines here. It is usually not desirable to denote the types arising from nesting records or collections in jOOQ. The above var result is inferred to:

Result<Record4<
    String,          // AUTHOR.FIRST_NAME
    String,          // AUTHOR.LAST_NAME
    Result<Record2<
        String,      // LANGUAGE.CD
        String       // LANGUAGE.DESCRIPTION
    >>,              // books
    Result<Record1<
        String       // BOOK_TO_BOOK_STORE.BOOK_STORE_NAME
    >>               // book_stores
>> result = ...

Notice also that in a lot of cases, using RecordMappers can be very helpful when nesting collections to DTO trees, especially when combined with ad hoc converters.

The result of the above query may look like this:

+----------+---------+-----------------------------+--------------------------------------------------+
|first_name|last_name|books                        |book_stores                                       |
+----------+---------+-----------------------------+--------------------------------------------------+
|George    |Orwell   |[(en, English)]              |[(Ex Libris), (Orell Füssli)]                     |
|Paulo     |Coelho   |[(de, Deutsch), (pt, {null})]|[(Buchhandlung im Volkshaus), (Ex Libris), (Ore...|
+----------+---------+-----------------------------+--------------------------------------------------+

Or, when exported as JSON (alternatively, use JSON_ARRAYAGG directly):

[
  {
    "first_name": "George",
    "last_name": "Orwell",
    "books": [
      {
        "cd": "en",
        "description": "English"
      }
    ],
    "book_stores": [
      { "book_store_name": "Ex Libris" },
      { "book_store_name": "Orell Füssli" }
    ]
  },
  {
    "first_name": "Paulo",
    "last_name": "Coelho",
    "books": [
      {
        "cd": "de",
        "description": "Deutsch"
      },
      {
        "cd": "pt",
        "description": null
      }
    ],
    "book_stores": [
      { "book_store_name": "Buchhandlung im Volkshaus" },
      { "book_store_name": "Ex Libris" },
      { "book_store_name": "Orell Füssli" }
    ]
  }
]

Or, when exported as XML (alternatively, use XMLAGG directly):

<result>
  <record>
    <first_name>George</first_name>
    <last_name>Orwell</last_name>
    <books>
      <result>
        <record>
          <cd>en</cd>
          <description>English</description>
        </record>
      </result>
    </books>
    <book_stores>
      <result>
        <record>
          <book_store_name>Ex Libris</book_store_name>
        </record>
        <record>
          <book_store_name>Orell Füssli</book_store_name>
        </record>
      </result>
    </book_stores>
  </record>
  <record>
    <first_name>Paulo</first_name>
    <last_name>Coelho</last_name>
    <books>
      <result>
        <record>
          <cd>de</cd>
          <description>Deutsch</description>
        </record>
        <record>
          <cd>pt</cd>
          <description/>
        </record>
      </result>
    </books>
    <book_stores>
      <result>
        <record>
          <book_store_name>Buchhandlung im Volkshaus</book_store_name>
        </record>
        <record>
          <book_store_name>Ex Libris</book_store_name>
        </record>
        <record>
          <book_store_name>Orell Füssli</book_store_name>
        </record>
      </result>
    </book_stores>
  </record>
</result>

Implementation

The bad news is, hardly any dialect supports the MULTISET constructor natively (e.g. Informix or Oracle do). In all other dialects, it has to be emulated using SQL/JSON or SQL/XML. The above query may look like this, in PostgreSQL:

SELECT
  AUTHOR.FIRST_NAME,
  AUTHOR.LAST_NAME,
  (
    SELECT COALESCE(
      JSONB_AGG(JSONB_BUILD_ARRAY(V0, V1)),
      JSONB_BUILD_ARRAY()
    )
    FROM (
      SELECT DISTINCT
        ALIAS_86077489.CD AS V0,
        ALIAS_86077489.DESCRIPTION AS V1
      FROM BOOK
        JOIN LANGUAGE AS ALIAS_86077489
          ON BOOK.LANGUAGE_ID = ALIAS_86077489.ID
      WHERE BOOK.AUTHOR_ID = AUTHOR.ID
    ) AS T
  ) AS BOOKS,
  (
    SELECT COALESCE(
      JSONB_AGG(JSONB_BUILD_ARRAY(V0)),
      JSONB_BUILD_ARRAY()
    )
    FROM (
      SELECT DISTINCT BOOK_TO_BOOK_STORE.BOOK_STORE_NAME AS V0
      FROM BOOK_TO_BOOK_STORE
        JOIN BOOK AS ALIAS_129518614
          ON BOOK_TO_BOOK_STORE.BOOK_ID = ALIAS_129518614.ID
      WHERE ALIAS_129518614.AUTHOR_ID = AUTHOR.ID
    ) AS T
  ) AS BOOK_STORES
FROM AUTHOR
ORDER BY AUTHOR.ID

As you might notice, this produces a slightly different JSON structure than what one might have created manually. It generates arrays of arrays, which look something like this in a formatted result:

|first_name|last_name|books                            |book_stores                                                     |
|----------|---------|---------------------------------|----------------------------------------------------------------|
|George    |Orwell   |[["en", "English"]]              |[["Ex Libris"], ["Orell Füssli"]]                               |
|Paulo     |Coelho   |[["de", "Deutsch"], ["pt", null]]|[["Buchhandlung im Volkshaus"], ["Ex Libris"], ["Orell Füssli"]]|

The benefits are:

  1. Arrays take less space than objects in JSON, so the serialisation format is more optimal
  2. Arrays don't care about duplicate column names, which can cause issues with various JSON parsers (even if JSON supports it)
  3. Array elements have a well defined order, object keys do not, and index lookups are faster than name lookups

The resulting JSON or XML document will be parsed and mapped to a jOOQ org.jooq.Result and org.jooq.Record hierarchy.

By default, the "best" serialisation format is used (JSON, XML, or ARRAY in the future), but you can override it using Settings.emulateMultiset, which offers the following values:

  • DEFAULT: Let jOOQ decide how to serialise nested collections
  • XML: Use XML to serialise nested collections
  • JSON: Use JSON to serialise nested collections
  • JSONB: Use JSONB to serialise nested collections
  • NATIVE: Generate a native syntax

Dialect support

This example using jOOQ:

multiset(select(BOOK.ID, BOOK.TITLE).from(BOOK))

Translates to the following dialect specific expressions:

Aurora Postgres, CockroachDB, Postgres, YugabyteDB

(
  SELECT coalesce(
    jsonb_agg(jsonb_build_array(v0, v1)),
    jsonb_build_array()
  )
  FROM (
    SELECT
      BOOK.ID v0,
      BOOK.TITLE v1
    FROM BOOK
  ) t
)

DB2

(
  SELECT xmlelement(
    NAME result,
    xmlagg(xmlelement(
      NAME record,
      xmlelement(NAME v0, BOOK.ID),
      xmlelement(
        NAME v1,
        xmlattributes(
          CASE
            WHEN BOOK.TITLE IS NULL THEN 'true'
          END AS xsi:nil
        ),
        BOOK.TITLE
      )
    ))
  )
  FROM BOOK
)

H2

(
  SELECT coalesce(
    json_arrayagg(json_array(BOOK.ID, BOOK.TITLE NULL ON NULL)),
    json_array(NULL ON NULL)
  )
  FROM BOOK
)

Informix

MULTISET(
  SELECT BOOK.ID, BOOK.TITLE
  FROM BOOK
)

MariaDB

(
  SELECT coalesce(
    json_merge_preserve(
      '[]',
      concat(
        '[',
        group_concat(json_array(BOOK.ID, BOOK.TITLE) SEPARATOR ','),
        ']'
      )
    ),
    json_array()
  )
  FROM BOOK
)

MySQL

(
  SELECT coalesce(
    json_merge_preserve(
      '[]',
      concat(
        '[',
        group_concat(json_array(t.v0, t.v1) SEPARATOR ','),
        ']'
      )
    ),
    json_array()
  )
  FROM (
    SELECT
      BOOK.ID v0,
      BOOK.TITLE v1
    FROM BOOK
  ) t
)

Oracle

(
  SELECT coalesce(
    json_arrayagg(json_array(t.v0, t.v1 NULL ON NULL RETURNING clob) FORMAT JSON RETURNING clob),
    json_array(RETURNING clob)
  )
  FROM (
    SELECT
      BOOK.ID v0,
      BOOK.TITLE v1
    FROM BOOK
  ) t
)

Snowflake

(
  SELECT coalesce(
    array_agg(array_construct(coalesce(
      to_variant(t.v0),
      parse_json('null')
    ), coalesce(
      to_variant(t.v1),
      parse_json('null')
    ))),
    array_construct()
  )
  FROM (
    SELECT
      BOOK.ID v0,
      BOOK.TITLE v1
    FROM BOOK
  ) t
)

SQLite

(
  SELECT coalesce(
    json_group_array(json_array(t.v0, t.v1)),
    json_array()
  )
  FROM (
    SELECT
      BOOK.ID v0,
      BOOK.TITLE v1
    FROM BOOK
  ) t
)

SQLServer

coalesce(
  (
    SELECT
      v0 ID,
      v1 TITLE
    FROM (
      SELECT
        BOOK.ID v0,
        BOOK.TITLE v1
      FROM BOOK
    ) t
    FOR XML RAW ('record'), ELEMENTS XSINIL, BINARY BASE64, TYPE, ROOT ('result')
  ),
  '<result/>'
)

Teradata

(
  SELECT xmlelement(
    NAME "result",
    xmlagg(xmlelement(
      NAME record,
      xmlelement(NAME v0, v0),
      xmlelement(
        NAME v1,
        xmlattributes(
          CASE
            WHEN v1 IS NULL THEN 'true'
          END AS nil
        ),
        v1
      )
    ))
  )
  FROM (
    SELECT
      BOOK.ID v0,
      BOOK.TITLE v1
    FROM BOOK
  ) t
)

Trino

(
  SELECT coalesce(
    cast(array_agg(CAST(ARRAY[
      CAST(t.v0 AS json),
      CAST(t.v1 AS json)
    ] AS json)) AS json),
    CAST(ARRAY[] AS json)
  )
  FROM (
    SELECT
      BOOK.ID v0,
      BOOK.TITLE v1
    FROM BOOK
  ) t
)

ASE, Access, Aurora MySQL, BigQuery, ClickHouse, Derby, DuckDB, Exasol, Firebird, HSQLDB, Hana, MemSQL, Redshift, SQLDataWarehouse, Sybase, Vertica

/* UNSUPPORTED */

(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