`JSON_OBJECT`

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Builds a JSON object from a list of key-value pairs.

<key>: The key for the pair. Keys are implicitly cast to strings and can’t be NULL.
<value>: The value for the pair. Values can be literals, column references, expressions, SQL arrays, or json-typed values.

FORMAT JSON: Applied to a string value, 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. NULL ON NULL (the default) includes keys whose value is NULL as JSON null. ABSENT ON NULL omits those keys.

Returns a json object. Keys are converted to JSON strings. String values are escaped and become JSON strings, while json-typed values and values marked with FORMAT JSON are embedded directly.

Keys are implicitly cast to strings. Timestamp keys use the XSD format, for example 2022-01-01T01:02:03.005.
A key can’t be NULL, an array, or a json-typed value.
An entry whose value is NULL is included by default, with the value rendered as JSON null. Use ABSENT ON NULL to omit these entries.
Duplicate keys are accepted by default. The WITH UNIQUE KEYS option isn’t supported.
With FORMAT JSON, invalid JSON is rejected with an invalid JSON document error.
The RETURNING clause from the SQL-standard isn’t supported.

Combine key-value pairs of different types into a JSON object.

Returns {"a":1,"b":true,"c":"my\"string"}.

Keys are implicitly cast to strings.

Returns {"1":1}.

By default, keys with NULL values are included. Use ABSENT ON NULL to omit them.

Returns {"a":null,"b":1} and {"b":1}.

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

Returns {"a":"{\"a\": 1}","x":1} and {"a":{"a": 1},"x":1}.

JSON Type
[JSON_ARRAY](./json-array.md) - Build a JSON array from a list of values.
[JSON_SCALAR](./json-scalar.md) - Convert a scalar value to JSON.
[json](./json-constructor.md) - Parse a string into a JSON value.