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

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.Result type, 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))

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, BigQuery, ClickHouse, Derby, Exasol, Firebird, HSQLDB, Hana, Informix, MemSQL, Redshift, SQLDataWarehouse, SQLServer, 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