ABSENT ON NULL
Supported by ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The JSON_OBJECTAGG function has a ABSENT ON NULL clause to indicate that NULL values should not be contained in the output JSON document. Instead, the key should be removed from the document if the value is NULL:
SELECT json_objectagg( language.cd, language.description ABSENT ON NULL ) FROM language
create.select(jsonObject(
LANGUAGE.CD, LANGUAGE.DESCRIPTION)
.absentOnNull())
.from(LANGUAGE)
.fetch();
The result would look like this:
+------------------+
| json_objectagg |
+------------------+
| {"EN":"English"} |
+------------------+
jsonObjectAgg(LANGUAGE.CD, LANGUAGE.DESCRIPTION).absentOnNull()
Translates to the following dialect specific expressions:
Aurora Postgres, Postgres, YugabyteDB
json_object_agg(LANGUAGE.CD, LANGUAGE.DESCRIPTION) FILTER (WHERE LANGUAGE.DESCRIPTION IS NOT NULL)
BigQuery, Spanner
json_strip_nulls(json_object( array_agg(LANGUAGE.CD), array_agg(LANGUAGE.DESCRIPTION) ))
CockroachDB
(('{' || string_agg(regexp_replace(cast(
CASE
WHEN LANGUAGE.DESCRIPTION IS NULL THEN cast(NULL AS json)
ELSE json_build_object(LANGUAGE.CD, LANGUAGE.DESCRIPTION)
END
AS string
), '^\{(.*)\}$', '\1', 'g'), ',') || '}'))
DB2
(('{' || listagg(
regexp_replace(cast(
CASE
WHEN LANGUAGE.DESCRIPTION IS NULL THEN NULL
ELSE json_object(KEY LANGUAGE.CD VALUE LANGUAGE.DESCRIPTION)
END
AS varchar(32672)
), '^\{(.*)\}$', '\1'),
','
) || '}'))
DuckDB
to_json(map_from_entries(array_agg(ROW(LANGUAGE.CD, LANGUAGE.DESCRIPTION)) FILTER (WHERE LANGUAGE.DESCRIPTION IS NOT NULL)))
H2, Oracle
json_objectagg(KEY LANGUAGE.CD VALUE LANGUAGE.DESCRIPTION ABSENT ON NULL)
MariaDB
(concat(
'{',
group_concat(regexp_replace(cast(
CASE
WHEN LANGUAGE.DESCRIPTION IS NULL THEN NULL
ELSE json_object(LANGUAGE.CD, LANGUAGE.DESCRIPTION)
END
AS char
), '^\\{(.*)\\}$', '\\1') SEPARATOR ','),
'}'
))
MySQL
(concat(
'{',
group_concat(regexp_replace(cast(
CASE
WHEN LANGUAGE.DESCRIPTION IS NULL THEN NULL
ELSE json_object(LANGUAGE.CD, LANGUAGE.DESCRIPTION)
END
AS char
), '^\\{(.*)\\}$', '$1') SEPARATOR ','),
'}'
))
Snowflake
object_agg(LANGUAGE.CD, LANGUAGE.DESCRIPTION)
SQLite
json_group_object(LANGUAGE.CD, LANGUAGE.DESCRIPTION) FILTER (WHERE LANGUAGE.DESCRIPTION IS NOT NULL)
Trino
cast(map(array_agg(cast(LANGUAGE.CD AS varchar)) FILTER (WHERE LANGUAGE.DESCRIPTION IS NOT NULL), array_agg(LANGUAGE.DESCRIPTION) FILTER (WHERE LANGUAGE.DESCRIPTION IS NOT NULL)) AS json)
ASE, Access, Aurora MySQL, ClickHouse, Databricks, Exasol, Firebird, HSQLDB, Hana, Informix, MemSQL, Redshift, SQLDataWarehouse, SQLServer, 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!