JSON_SCALAR

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Converts a value to its JSON representation.

1json_scalar(<value>)

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

1SELECT
2  json_scalar(-1::int) AS number,
3  json_scalar(true) AS boolean,
4  json_scalar('{"a":1}'::text) AS string;

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

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

1SELECT json_scalar('{"a":1}'::json) AS result;

Returns {"a":1}.

Date and time values use the JSON-friendly format.

1SELECT
2  json_scalar('2022-01-01'::date) AS date_value,
3  json_scalar('2022-01-01 01:02:03.005'::timestamp) AS timestamp_value;

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

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

1SELECT
2  json_scalar('[-1, 1]'::int[]) AS array_value,
3  json_scalar(ROW(42 AS a, 'text' AS b, true AS c)) AS row_value;

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

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

1SELECT json_scalar(NULL::int) IS NULL AS is_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.