JSON_SCALAR

Applies to: ✅ Data 360 SQL ✅ Tableau Hyper API

Converts a value to its JSON representation.

Syntax

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

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

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

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

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

Returns {"a":1}.

Date and time values use the JSON-friendly format.

SELECT
  json_scalar('2022-01-01'::date) AS date_value,
  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.

SELECT
  json_scalar('[-1, 1]'::int[]) AS array_value,
  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.

SELECT json_scalar(NULL::int) IS NULL AS is_null;

Returns true.