JSON_ARRAY
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
The JSON_ARRAY function is used to produce simple JSON arrays from scalar values, without aggregation (see also JSON_ARRAYAGG)
SELECT json_array(author.first_name, author.last_name) FROM author
create.select(jsonArray(AUTHOR.FIRST_NAME, AUTHOR.LAST_NAME))
.from(AUTHOR)
.fetch();
The result would look like this:
+----------------------+ | json_array | +----------------------+ | ["Paulo", "Coelho"] | | ["George", "Orwell"] | +----------------------+
Dialect support
This example using jOOQ:
jsonArray(val(1), val(2))
Translates to the following dialect specific expressions:
Aurora Postgres, Postgres, YugabyteDB
json_build_array(CAST(1 AS int), CAST(2 AS int))
ClickHouse
toJSONString(tuple(1, 2))
CockroachDB
json_build_array(CAST(1 AS int4), CAST(2 AS int4))
DB2, H2, MariaDB, MySQL, Oracle, SQLite
json_array(1, 2)
Snowflake
array_construct(coalesce(
to_variant(1),
parse_json('null')
), coalesce(
to_variant(2),
parse_json('null')
))
SQLServer
json_modify(
json_modify('[]', 'append $', 1),
'append $',
2
)
Trino
CAST(ARRAY[ CAST(1 AS json), CAST(2 AS json) ] AS json)
ASE, Access, Aurora MySQL, BigQuery, Derby, DuckDB, Exasol, Firebird, HSQLDB, Hana, Informix, MemSQL, Redshift, SQLDataWarehouse, 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!