JSON Functions

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Hyper supports the SQL:2016 functions for constructing JSON values from SQL data. Use JSON functions to:

• Build JSON arrays from a list of values.
• Build JSON objects from key-value pairs.
• Convert any scalar value to its JSON representation.
• Parse and embed pre-formatted JSON documents.

Build JSON values from SQL data:

• [json](./json-constructor.md) - Parse a string into a JSON value.
• [json_array](./json-array.md) - Build a JSON array from a list of values.
• [json_object](./json-object.md) - Build a JSON object from key-value pairs.
• [json_scalar](./json-scalar.md) - Convert any scalar value to JSON.

• Strings passed as arguments are escaped and become JSON string values. To embed a value as raw JSON instead, pass a json-typed value or use the FORMAT JSON modifier.
• The RETURNING clause and the WITH UNIQUE KEYS option from the SQL:2016 standard aren’t supported.

Combine json_object and json_array to build a nested document from table columns.

1SELECT json_object(
2  'id': c.id,
3  'name': c.name,
4  'phone_numbers': json_array(
5    c.home_phone,
6    c.work_phone,
7    c.mobile_phone
8    ABSENT ON NULL
9  )
10) AS customer_summary
11FROM customers c;

Returns {"id":42,"name":"Alfons","phone_numbers":["(415) 555-0134"]} for a customer with only a mobile number on file.

Use the json constructor to embed an existing JSON document without escaping it.

1SELECT json_object('a': 1, 'b': json('{"nested":true}')) AS result;

Returns {"a":1,"b":{"nested":true}}.

• JSON Type
• JSON_ARRAYAGG - Aggregate values from a group into a JSON array.
• Array Functions