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)
-- 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)
-- ACCESS, ASE, AURORA_MYSQL, BIGQUERY, DERBY, DUCKDB, EXASOL, FIREBIRD, HANA, HSQLDB, INFORMIX, MEMSQL, REDSHIFT,
-- SQLDATAWAREHOUSE, SQLSERVER, SYBASE, TERADATA, VERTICA
/* UNSUPPORTED */
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!