Available in versions: Dev (3.16) | Latest (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, YUGABYTE
jsonb_agg(jsonb_build_array(BOOK.ID, BOOK.TITLE))

-- DB2, TERADATA
xmlelement(
  NAME result,
  xmlagg(xmlelement(
    NAME record,
    xmlelement(NAME v0, BOOK.ID),
    xmlelement(NAME v1, 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)

-- SQLITE
json_group_array(json_array(BOOK.ID, BOOK.TITLE))

-- ACCESS, ASE, AURORA_MYSQL, BIGQUERY, DERBY, EXASOL, FIREBIRD, HANA, HSQLDB, INFORMIX, MEMSQL, REDSHIFT, SNOWFLAKE, 
-- SQLDATAWAREHOUSE, SQLSERVER, 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