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"} |
+------------------------+---------------+
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)
BigQuery
json_object( array_agg(AUTHOR.FIRST_NAME), array_agg(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'),
','
) || '}'))
DuckDB
to_json(map_from_entries(array_agg(ROW(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME))))
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, ClickHouse, Derby, Exasol, Firebird, HSQLDB, Hana, Informix, MemSQL, Redshift, SQLDataWarehouse, SQLServer, Sybase, Teradata, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.20. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!