JSON_ARRAY

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Builds a JSON array from a list of values.

Syntax

json_array([<value> [FORMAT JSON] [, ...]] [{NULL | ABSENT} ON NULL])

Arguments

Required

<value>: A value to include in the array. Provide zero or more values separated by commas. Values can be literals, column references, expressions, SQL arrays, or json-typed values.

Optional

FORMAT JSON: Applied to a string argument, treats the string as a pre-formatted JSON document and embeds it without escaping. Invalid JSON raises an error.
NULL ON NULL / ABSENT ON NULL: Controls how NULL values are handled. ABSENT ON NULL (the default) skips NULL values. NULL ON NULL includes them as JSON null.

Returns

Returns a json array. String values are escaped and become JSON strings. json-typed values and values marked with FORMAT JSON are embedded directly. SQL arrays become nested JSON arrays.

Considerations

By default, NULL values are skipped. Use NULL ON NULL to include them.
A string argument is escaped into a JSON string unless it's cast to json or marked with FORMAT JSON.
With FORMAT JSON, invalid JSON is rejected with an invalid JSON document error.
SQL array arguments are converted to nested JSON arrays.
The RETURNING clause from the SQL-standard isn't supported.
Some types can't be converted, such as bytes.

Examples

Build a Simple Array

Combine values of different types into a JSON array.

SELECT json_array(1, true, 'my"string') AS result;

Returns [1,true,"my\"string"].

Control NULL Handling

By default, NULL values are skipped. Use NULL ON NULL to keep them.

SELECT
  json_array(null::int, 1, null::int) AS default_behavior,
  json_array(null::int, 1, null::int NULL ON NULL) AS keep_nulls;

Returns [1] and [null,1,null].

Embed Pre-Formatted JSON

Use FORMAT JSON or a ::json cast to treat a string as raw JSON instead of escaping it.

SELECT
  json_array(1, '{"a": 1}', 3) AS escaped,
  json_array(1, '{"a": 1}' FORMAT JSON, 3) AS embedded1,
  json_array(1, '{"a": 1}'::json, 3) AS embedded2;

Returns [1,"{\"a\": 1}",3] and [1,{"a": 1},3].

Nest SQL Arrays

SQL array arguments become nested JSON arrays.

SELECT json_array('[1, -1, null]'::smallint[], 'test') AS result;

Returns [[1,-1,null],"test"].