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))
-- 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)
-- ACCESS, ASE, AURORA_MYSQL, BIGQUERY, DERBY, DUCKDB, EXASOL, FIREBIRD, HANA, HSQLDB, INFORMIX, MEMSQL, REDSHIFT,
-- SQLDATAWAREHOUSE, SYBASE, TERADATA, VERTICA
/* UNSUPPORTED */
(These are currently generated with jOOQ 3.19, see #10141), or translate your own on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!