JSON_SCALAR

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Converts a value to its JSON representation.

• <value>: The value to convert. Can be a number, Boolean, string, date/time value, json value, SQL array, or row.

Returns a json value.

• Numbers, Booleans, and json values are represented directly.
• Any other scalar uses its text representation, escaped as needed to form a valid JSON string.
• SQL arrays become JSON arrays, converted recursively.
• Rows become JSON objects.
• A NULL input returns SQL NULL, not the JSON literal null.
• Values already typed as json are forwarded unchanged.

• The conversion is similar to a text cast, but uses a slightly different format for dates and timestamps. Dates use the form 2022-01-01, timestamps use 2022-01-01T01:02:03.005, and timestamps with time zone include the offset, for example 2022-01-01T01:02:03.005+00:00.
• Special floating-point values are represented as JSON strings: "NaN", "Infinity", and "-Infinity". The values 0 and -0 are distinguished.
• bigint and numeric values are printed in full, even when they aren’t exactly representable as JavaScript numbers.
• Some types can’t be converted, such as bytes.

Numbers and Booleans are represented directly, while strings are escaped into JSON strings.

Returns -1, true, and "{\"a\":1}".

A json-typed value is preserved as JSON rather than escaped.

Returns {"a":1}.

Date and time values use the JSON-friendly format.

Returns "2022-01-01" and "2022-01-01T01:02:03.005".

SQL arrays become JSON arrays, and rows become JSON objects.

Returns [-1,1] and {"a":42,"b":"text","c":true}.

A NULL input returns SQL NULL, not the JSON literal null.

Returns true.

• JSON Type
• [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](./json-constructor.md) - Parse a string into a JSON value.