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:
- Arrays take less space than objects in JSON, so the serialisation format is more optimal
- Arrays don't care about duplicate column names, which can cause issues with various JSON parsers (even if JSON supports it)
- 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
)
BigQuery, Informix
MULTISET( SELECT BOOK.ID, BOOK.TITLE FROM BOOK )
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
)
DuckDB
ARRAY(
SELECT t
FROM (
SELECT BOOK.ID, BOOK.TITLE
FROM BOOK
) t
)
H2
(
SELECT coalesce(
json_arrayagg(json_array(BOOK.ID, BOOK.TITLE NULL ON NULL)),
json_array(NULL ON NULL)
)
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, BOOK.TITLE
FROM BOOK
) t (v0, v1)
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, ClickHouse, Derby, Exasol, Firebird, HSQLDB, Hana, MemSQL, Redshift, SQLDataWarehouse, Sybase, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!