JSON_OBJECT

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

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

1json_object([<key>: <value> [FORMAT JSON] [, ...]] [{NULL | ABSENT} ON NULL] [WITHOUT UNIQUE KEYS])

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

1SELECT json_object('a': 1, 'b': true, 'c': 'my"string') AS result;

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

Keys are implicitly cast to strings.

1SELECT json_object(1: 1) AS result;

Returns {"1":1}.

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

1SELECT
2  json_object('a': null::int, 'b': 1) AS default_behavior,
3  json_object('a': null::int, 'b': 1 ABSENT ON NULL) AS drop_nulls;

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.

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

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.