Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12
This documentation is for the unreleased development version of jOOQ. Click on the above version links to get this documentation for a supported version of jOOQ.
NULL ON NULL
Supported by ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The JSON_ARRAY function has a NULL ON NULL clause to indicate that NULL values be included in the output JSON array:
SELECT json_array( language.cd, language.description NULL ON NULL ) FROM language
create.select(jsonArray(
LANGUAGE.CD, LANGUAGE.DESCRIPTION)
.nullOnNull())
.from(LANGUAGE)
.fetch();
The result would look like this:
+------------------+ | json_array | +------------------+ | ["EN","English"] | | ["DE", null] | +------------------+
Dialect support
This example using jOOQ:
jsonArray(LANGUAGE.CD, LANGUAGE.DESCRIPTION).nullOnNull()
Translates to the following dialect specific expressions:
Aurora Postgres, CockroachDB, Postgres, YugabyteDB
json_build_array(LANGUAGE.CD, LANGUAGE.DESCRIPTION)
BigQuery, DuckDB, MariaDB, MySQL, SQLite, Spanner
json_array(LANGUAGE.CD, LANGUAGE.DESCRIPTION)
ClickHouse
toJSONString(tuple(LANGUAGE.CD, LANGUAGE.DESCRIPTION))
DB2
( SELECT json_array(LANGUAGE.CD, LANGUAGE.DESCRIPTION NULL ON NULL) FROM SYSIBM.DUAL )
H2, Oracle, SQLServer
json_array(LANGUAGE.CD, LANGUAGE.DESCRIPTION NULL ON NULL)
Snowflake
array_construct(coalesce(
to_variant(LANGUAGE.CD),
parse_json('null')
), coalesce(
to_variant(LANGUAGE.DESCRIPTION),
parse_json('null')
))
Trino
cast(
ARRAY[
cast(
cast(LANGUAGE.CD AS varchar)
AS json
),
cast(LANGUAGE.DESCRIPTION AS json)
]
AS json
)
ASE, Access, Aurora MySQL, 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!