| The jOOQ User Manual : SQL building : Column expressions : Aggregate functions : JSON_ARRAYAGG | previous : next |
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
json_agg(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 ','), ']'))
-- ACCESS, ASE, AURORA_MYSQL, BIGQUERY, DERBY, EXASOL, FIREBIRD, HANA, HSQLDB, IGNITE, INFORMIX, MEMSQL, REDSHIFT, SNOWFLAKE,
-- SQLDATAWAREHOUSE, SQLITE, SQLSERVER, SYBASE, TERADATA, VERTICA
/* UNSUPPORTED */
(These are currently generated with jOOQ 3.15, see #10141)
Feedback
Do you have any feedback about this page? We'd love to hear it!