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

JSON_OBJECTAGG

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 object using JSON_OBJECTAGG

SELECT json_objectagg(
  CAST(author.id AS varchar(100)),
  first_name
)
FROM author
 
create.select(jsonObjectAgg(
         cast(AUTHOR.ID, VARCHAR(100)),
         AUTHOR.FIRST_NAME
      ))
      .from(AUTHOR)
      .fetch();

The result would look like this:

+----------------------------+
| json_objectagg             |
+----------------------------+
| {"1":"George","2":"Paulo"} |
+----------------------------+

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_objectagg(
    CAST(author.id AS varchar(100)),
    nullif(first_name, 'George')
    NULL ON NULL
  ) AS c1,
  json_objectagg(
    CAST(author.id AS varchar(100)),
    nullif(first_name, 'George')
    ABSENT ON NULL
  ) AS c2
FROM author
create.select(
         jsonObjectAgg(
           cast(AUTHOR.ID, VARCHAR(100)),
           nullif(AUTHOR.FIRST_NAME, "George")
         ).nullOnNull().as("c1"),
         jsonObjectAgg(
           cast(AUTHOR.ID, VARCHAR(100)),
           nullif(AUTHOR.FIRST_NAME, "George")
         ).absentOnNull().as("c2")
       )
      .from(AUTHOR)
      .fetch();

The result would look like this:

+------------------------+---------------+
| C1                     | C2            |
+------------------------+---------------+
| {"1":null,"2":"Paulo"} | {"2":"Paulo"} |
+------------------------+---------------+

The effect is similar to that of the FILTER clause.

Dialect support

This example using jOOQ:

jsonObjectAgg(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)

Translates to the following dialect specific expressions:

Aurora Postgres, Postgres, YugabyteDB

json_object_agg(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)

CockroachDB

((('{' || string_agg(regexp_replace(CAST(json_build_object(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME) AS string), '^\{(.*)\}$', '\1', 'g'), ',')) || '}'))

DB2

((('{' || listagg(
  regexp_replace(CAST(json_object(KEY AUTHOR.FIRST_NAME VALUE AUTHOR.LAST_NAME) AS varchar(32672)), '^\{(.*)\}$', '\1'),
  ','
)) || '}'))

H2, Oracle

json_objectagg(KEY AUTHOR.FIRST_NAME VALUE AUTHOR.LAST_NAME)

MariaDB, MySQL

json_objectagg(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)

Snowflake

object_agg(coalesce(
  to_variant(AUTHOR.FIRST_NAME),
  parse_json('null')
), coalesce(
  to_variant(AUTHOR.LAST_NAME),
  parse_json('null')
))

SQLite

json_group_object(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME)

Trino

cast(map(array_agg(AUTHOR.FIRST_NAME), array_agg(AUTHOR.LAST_NAME)) 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