Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17 | 3.16 | 3.15 | 3.14

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] |
+----------+-----+

The effect is similar to that of the FILTER clause.

Dialect support

This example using jOOQ:

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)

ASE, Access, Aurora MySQL, BigQuery, ClickHouse, Derby, DuckDB, Exasol, Firebird, HSQLDB, Hana, Informix, MemSQL, Redshift, SQLDataWarehouse, SQLServer, Sybase, Teradata, Vertica

/* UNSUPPORTED */

(These are currently generated with jOOQ 3.20, see #10141), or translate your own on our website

References to this page

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo