JSON Type

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

The json type stores a JSON (JavaScript Object Notation) value, such as an object, array, string, number, boolean, or null. Use it to model semi-structured data in a single SQL value, for example nested attributes that don’t map cleanly to columns.

You can create json values in these ways.

• By parsing a string with the [json](../scalar-func/json-func/json-constructor.md) constructor or a cast (see Parsing and Validation below).
• By building values from SQL data with [json_array](../scalar-func/json-func/json-array.md), [json_object](../scalar-func/json-func/json-object.md), or [json_scalar](../scalar-func/json-func/json-scalar.md).

For a comprehensive overview of supported operations, see JSON Functions.

You can convert a string to json with the json constructor, or with a cast using CAST, TRY_CAST, or :: (see Type Conversions for the general cast syntax). All three forms validate that the string is a well-formed JSON document.

1SELECT '{"a":1}'::json;

Returns {"a":1}.

1SELECT CAST('{]' AS json);

Raises an error because the input isn’t valid JSON.

1SELECT TRY_CAST('{]' AS json);

Returns NULL instead of raising an error.

Validation follows the JSON standard.

• Numbers in scientific notation are accepted, for example 1e2, 1E+2, and 1e-2. Malformed numbers such as 2.e+3, 0.e1, or [1.] are rejected.
• Control characters inside strings must be escaped. The DEL control character (0x7F) doesn’t require escaping.

• When a json value is embedded into another JSON value by [json_array](../scalar-func/json-func/json-array.md) or [json_object](../scalar-func/json-func/json-object.md), it’s copied in directly rather than escaped as a string.

• JSON Functions - Overview of all JSON construction functions.
• [json](../scalar-func/json-func/json-constructor.md) - Parse a string into a JSON value.
• Type Conversions - Type casting and coercion.
• Data Types - Overview of all Data 360 SQL types.