Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
ARRAY_AGG
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The ARRAY_AGG aggregate function aggregates grouped values into an array. It supports being used with an ORDER BY clause.
SELECT array_agg(ID) array_agg(ID ORDER BY ID DESC) FROM BOOK
create.select(
arrayAgg(BOOK.ID),
arrayAgg(BOOK.ID).orderBy(BOOK.ID.desc()))
.from(BOOK)
Producing:
+--------------+--------------+ | array_agg | array_agg | +--------------+--------------+ | [1, 3, 4, 2] | [4, 3, 2, 1] | +--------------+--------------+
Unlike the MULTISET_AGG function, this:
- Produces an array, instead of a
org.jooq.Resulttype - Allows for projecting only a single column (though that column may contain a nested record)
Dialect support
This example using jOOQ:
arrayAgg(BOOK.ID)
Translates to the following dialect specific expressions:
Aurora Postgres, BigQuery, CockroachDB, Databricks, DuckDB, H2, HSQLDB, Postgres, Trino, YugabyteDB
array_agg(BOOK.ID)
ClickHouse
groupArray(BOOK.ID)
ASE, Access, Aurora MySQL, DB2, Derby, Exasol, Firebird, Hana, Informix, MariaDB, MemSQL, MySQL, Oracle, Redshift, SQLDataWarehouse, SQLServer, SQLite, Snowflake, Sybase, Teradata, 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!