Categories: Variant Type

TO_JSON Preview

Converts a VARIANT value to a JSON string.

Syntax

TO_JSON(variant VARIANT) → VARCHAR

• variant: The VARIANT value to convert to a JSON string.

Examples

TO_JSON example

SELECT TO_JSON(TO_VARIANT(42))
-- 42

TO_JSON example

SELECT TO_JSON(TO_VARIANT('hello'))
-- "hello"

TO_JSON example

SELECT TO_JSON(PARSE_JSON('{"name": "Alice"}'))
-- {"name":"Alice"}

TO_JSON example

SELECT TO_JSON(TO_VARIANT(DATE '2024-01-15'))
-- "2024-01-15"

Usage Notes

TO_JSON produces a valid JSON string representation of the VARIANT value. This is useful for:

• Displaying VARIANT data in a readable format
• Exporting VARIANT data as JSON
• Debugging VARIANT contents

Type representation in JSON

Different VARIANT types are represented as follows in the output JSON:

VARIANT type	JSON representation
NULL	null
BOOLEAN	true or false
INT, BIGINT	Number without fraction (e.g., 12345)
FLOAT, DOUBLE	Number with fraction and exponent (e.g., 1.23456E+2)
DECIMAL	Number with fraction matching scale (e.g., 123.456000)
DATE	String in YYYY-MM-DD format
TIME	String in hh:mm:ss.ssssss format (microsecond precision)
TIMESTAMP	String in YYYY-MM-DD hh:mm:ss.ssssss format
TIMESTAMP (nanos)	String in YYYY-MM-DD hh:mm:ss.sssssssss format
TIMESTAMP with timezone	String in YYYY-MM-DD hh:mm:ss.ssssss+00:00 format
TIMESTAMP with timezone (nanos)	String in YYYY-MM-DD hh:mm:ss.sssssssss+00:00 format
VARBINARY	Base64-encoded string
UUID	String in xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx format
VARCHAR	JSON string
LIST	JSON array
STRUCT	JSON object

TIMESTAMP with timezone values can appear in variant data when reading Iceberg tables written by external engines such as Spark or Flink that use the timestamptz column type.

Precision for time values

Variant time values store microsecond precision. When cast to Dremio's TIME type, precision is truncated to milliseconds. Use TO_JSON to preserve the original microsecond precision as a string.

The +00:00 suffix on timezone-aware timestamps indicates that the value is UTC-encoded. It does not represent a user-local timezone, and Dremio does not store or convert timezone information.