ABSENT ON NULL
Supported by ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The JSON_ARRAYAGG function has a ABSENT ON NULL clause to indicate that NULL values should not be contained in the output JSON array. Instead, the values should be removed from the array if they are NULL:
SELECT json_arrayagg( language.description ABSENT ON NULL ) FROM language
create.select(jsonArrayAgg(
LANGUAGE.DESCRIPTION)
.absentOnNull())
.from(LANGUAGE)
.fetch();
The result would look like this:
+---------------+ | json_arrayagg | +---------------+ | ["English"] | +---------------+
jsonArrayAgg(LANGUAGE.DESCRIPTION).absentOnNull()
Translates to the following dialect specific expressions:
Aurora MySQL, H2, Oracle
json_arrayagg(LANGUAGE.DESCRIPTION ABSENT ON NULL)
Aurora Postgres, CockroachDB, Postgres, YugabyteDB
json_agg(LANGUAGE.DESCRIPTION) FILTER (WHERE LANGUAGE.DESCRIPTION IS NOT NULL)
BigQuery, Spanner
json_strip_nulls(to_json(
coalesce(
array_agg(LANGUAGE.DESCRIPTION), cast(ARRAY[] AS array<string>)
)
))
DuckDB
to_json(array_agg(LANGUAGE.DESCRIPTION) FILTER (WHERE LANGUAGE.DESCRIPTION IS NOT NULL))
MariaDB, MySQL
json_merge_preserve(
'[]',
concat(
'[',
group_concat(json_quote(LANGUAGE.DESCRIPTION) SEPARATOR ','),
']'
)
)
Snowflake
array_agg(LANGUAGE.DESCRIPTION)
SQLite
json_group_array(LANGUAGE.DESCRIPTION) FILTER (WHERE LANGUAGE.DESCRIPTION IS NOT NULL)
SQLServer
json_query(('[' + string_agg(cast(
('"' + replace(LANGUAGE.DESCRIPTION, '"', '\"') + '"')
AS varchar(max)
), ',') + ']'))
Trino
cast(array_agg(LANGUAGE.DESCRIPTION) FILTER (WHERE LANGUAGE.DESCRIPTION IS NOT NULL) AS json)
ASE, Access, ClickHouse, DB2, Databricks, Exasol, Firebird, HSQLDB, Hana, Informix, MemSQL, Redshift, SQLDataWarehouse, Sybase, Teradata, Vertica
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!