JSON_OBJECT

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

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

Syntax

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

Arguments

Required

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

Optional

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

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.

Considerations

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.

Examples

Build a Simple Object

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

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

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

Non-String Keys

Keys are implicitly cast to strings.

SELECT json_object(1: 1) AS result;

Returns {"1":1}.

Control NULL Handling

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

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

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

Embed Pre-Formatted JSON

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

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

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