JSON_ARRAYAGG
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A data set can be aggregated into a org.jooq.JSON or org.jooq.JSONB array using JSON_ARRAYAGG
SELECT json_arrayagg(author.id) FROM author
create.select(jsonArrayAgg(AUTHOR.ID))
.from(AUTHOR)
.fetch();
The result would look like this:
+---------------+ | json_arrayagg | +---------------+ | [1,2] | +---------------+
Ordering aggregation contents
When aggregating data into an array or JSON array, ordering may be relevant. For this, use the ORDER BY clause in JSON_ARRAYAGG
SELECT json_arrayagg(author.id ORDER BY author.id DESC) FROM author
create.select(jsonArrayAgg(AUTHOR.ID).orderBy(AUTHOR.ID.desc())
.from(AUTHOR)
.fetch();
The result would look like this:
+---------------+ | json_arrayagg | +---------------+ | [2,1] | +---------------+
NULL handling
Some dialects support the SQL standard NULL ON NULL and ABSENT ON NULL syntax, which allows for including / excluding NULL values from aggregation. By default, SQL aggregate functions always exclude NULL values, but in the context of JSON data types, NULL may have a different significance:
SELECT json_arrayagg(nullif(author.id, 1) NULL ON NULL) AS c1, json_arrayagg(nullif(author.id, 1) ABSENT ON NULL) AS c2 FROM author
create.select(
jsonArrayAgg(nullif(AUTHOR.ID, 1)).nullOnNull() .as("c1"),
jsonArrayAgg(nullif(AUTHOR.ID, 1)).absentOnNull().as("c2"))
.from(AUTHOR)
.fetch();
The result would look like this:
+----------+-----+ | C1 | C2 | +----------+-----+ | [null,2] | [2] | +----------+-----+
jsonArrayAgg(AUTHOR.ID)
Translates to the following dialect specific expressions:
Aurora Postgres, CockroachDB, Postgres, YugabyteDB
json_agg(AUTHOR.ID)
ClickHouse
toJSONString(groupArray(AUTHOR.ID))
DB2
CAST((('[' || listagg(
AUTHOR.ID,
','
)) || ']') AS varchar(32672))
H2, Oracle
json_arrayagg(AUTHOR.ID)
MariaDB, MySQL
json_merge_preserve(
'[]',
concat(
'[',
group_concat(AUTHOR.ID SEPARATOR ','),
']'
)
)
Snowflake
array_agg(coalesce(
to_variant(AUTHOR.ID),
parse_json('null')
))
SQLite
json_group_array(AUTHOR.ID)
Trino
cast(array_agg(AUTHOR.ID) AS json)
ASE, Access, Aurora MySQL, BigQuery, Derby, DuckDB, Exasol, Firebird, HSQLDB, Hana, Informix, MemSQL, Redshift, SQLDataWarehouse, SQLServer, Sybase, Teradata, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!