JSON_ARRAY

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Builds a JSON array from a list of values.

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

• <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.

• 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 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.

• 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.

Combine values of different types into a JSON array.

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

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

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

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

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

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

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

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

SQL array arguments become nested JSON arrays.

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

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

• JSON Type
• JSON_OBJECT - Build a JSON object from key-value pairs.
• JSON_SCALAR - Convert a scalar value to JSON.
• json - Parse a string into a JSON value.