This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
MULTISET_AGG
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The synthetic MULTISET_AGG() aggregate function collects group contents into a nested collection, just like the MULTISET value constructor (learn about other synthetic sql syntaxes).
SELECT
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
MULTISET_AGG(
BOOK.ID,
BOOK.TITLE,
LANGUAGE.CD
)
FROM AUTHOR
JOIN BOOK ON AUTHOR.ID = BOOK.AUTHOR_ID
JOIN LANGUAGE ON BOOK.LANGUAGE_ID = LANGUAGE.ID
GROUP BY
AUTHOR.ID,
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME
ORDER BY AUTHOR.ID
create.select(
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME,
multisetAgg(
BOOK.ID,
BOOK.TITLE,
BOOK.language().CD
).as("books"))
.from(AUTHOR)
.join(BOOK).on(AUTHOR.ID.eq(BOOK.AUTHOR_ID))
.groupBy(
AUTHOR.ID,
AUTHOR.FIRST_NAME,
AUTHOR.LAST_NAME)
.orderBy(AUTHOR.ID)
.fetch()
The result being:
+----------+---------+---------------------------------------+ |first_name|last_name|books | +----------+---------+---------------------------------------+ |George |Orwell |[(2, Animal Farm, en), (1, 1984, en)] | |Paulo |Coelho |[(4, Brida, de), (3, O Alquimista, pt)]| +----------+---------+---------------------------------------+
Unlike the ARRAY_AGG function, this:
- Produces a more convenient
org.jooq.Resulttype, instead of an array - Allows for projecting multiple columns in a type safe way, instead of just a single column
Dialect support
This example using jOOQ:
multisetAgg(BOOK.ID, BOOK.TITLE)
Translates to the following dialect specific expressions:
Aurora Postgres, CockroachDB, Postgres, YugabyteDB
jsonb_agg(jsonb_build_array(BOOK.ID, BOOK.TITLE))
BigQuery
array_agg(`nested__ID`, `nested__TITLE`)
DB2
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
)
))
)
DuckDB
array_agg(ROW (BOOK.ID, BOOK.TITLE))
H2
json_arrayagg(json_array(BOOK.ID, BOOK.TITLE NULL ON NULL))
MariaDB, MySQL
json_merge_preserve(
'[]',
concat(
'[',
group_concat(json_array(BOOK.ID, BOOK.TITLE) SEPARATOR ','),
']'
)
)
Oracle
json_arrayagg(json_array(BOOK.ID, BOOK.TITLE NULL ON NULL RETURNING clob) FORMAT JSON RETURNING clob)
Snowflake
array_agg(array_construct(coalesce(
to_variant(BOOK.ID),
parse_json('null')
), coalesce(
to_variant(BOOK.TITLE),
parse_json('null')
)))
SQLite
json_group_array(json_array(BOOK.ID, BOOK.TITLE))
Teradata
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 nil
),
BOOK.TITLE
)
))
)
Trino
cast(array_agg(CAST(ARRAY[ CAST(BOOK.ID AS json), CAST(BOOK.TITLE AS json) ] AS json)) AS json)
ASE, Access, Aurora MySQL, ClickHouse, Databricks, Derby, Exasol, Firebird, HSQLDB, Hana, Informix, MemSQL, Redshift, SQLDataWarehouse, SQLServer, Sybase, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!