JSON_ARRAY

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Builds a JSON array from a list of values.

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

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

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

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

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

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

SQL array arguments become nested JSON arrays.

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