New versions: Dev (3.16) | Latest (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_object(
        'cd', t.cd,
        'description', t.DESCRIPTION
      )),
      jsonb_build_array()
    )
    FROM (
      SELECT DISTINCT alias_86077489.CD, alias_86077489.DESCRIPTION
      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_object('BOOK_STORE_NAME', t.BOOK_STORE_NAME)),
      jsonb_build_array()
    )
    FROM (
      SELECT DISTINCT BOOK_TO_BOOK_STORE.BOOK_STORE_NAME
      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

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, YUGABYTE
(
  SELECT coalesce(
    jsonb_agg(jsonb_build_array(v0, v1)),
    jsonb_build_array()
  )
  FROM (
    SELECT
      BOOK.ID AS v0,
      BOOK.TITLE AS v1
    FROM BOOK
  ) AS t
)

-- DB2, TERADATA
(
  SELECT xmlelement(
    NAME result,
    xmlagg(xmlelement(
      NAME record,
      xmlelement(NAME v0, v0),
      xmlelement(NAME v1, v1)
    ))
  )
  FROM (
    SELECT
      BOOK.ID v0,
      BOOK.TITLE v1
    FROM BOOK
  ) t
)

-- H2
(
  SELECT coalesce(
    json_arrayagg(json_array(v0, v1 NULL ON NULL)),
    json_array(NULL ON NULL)
  )
  FROM (
    SELECT
      BOOK.ID v0,
      BOOK.TITLE v1
    FROM BOOK
  ) t
)

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

-- ORACLE
(
  SELECT coalesce(
    json_arrayagg(json_array(v0, 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
)

-- SQLITE
(
  SELECT coalesce(
    json_group_array(json_array(v0, v1)),
    json_array()
  )
  FROM (
    SELECT
      BOOK.ID AS v0,
      BOOK.TITLE AS v1
    FROM BOOK
  ) AS 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, TYPE, ROOT ('result')
  ),
  '<result/>'
)

-- ACCESS, ASE, AURORA_MYSQL, BIGQUERY, DERBY, EXASOL, FIREBIRD, HANA, HSQLDB, INFORMIX, MEMSQL, REDSHIFT, SNOWFLAKE, 
-- SQLDATAWAREHOUSE, SYBASE, VERTICA
/* UNSUPPORTED */

(These are currently generated with jOOQ 3.16, 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