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, 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!