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, Derby, Exasol, Firebird, HSQLDB, Hana, Informix, MemSQL, Redshift, SQLDataWarehouse, SQLServer, Sybase, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
References to this page
Feedback
Do you have any feedback about this page? We'd love to hear it!