TO_JSON_STRING

Reference material for TO_JSON_STRING function

Converts SQL STRUCT value to a valid JSON string representation. Struct field names become JSON object keys, and nested structures are converted to nested JSON objects and arrays.

Syntax

TO_JSON_STRING(<input>)

Parameters

Parameter	Description	Supported input types
<input>	Any STRUCT expression to convert to JSON string format.	STRUCT

Return Type

TEXT

• If the input is NULL, the function returns NULL.
• The function always produces a valid JSON string for non-null inputs.

Type Conversions

The following table shows how Firebolt data types are converted to JSON:

Firebolt Type	JSON Representation	Example
TEXT	JSON string	'hello' → "hello"
BYTEA	Hex string with \x prefix	'string'::BYTEA → "\\x737472696e67"
BOOLEAN	JSON boolean	true → true, false → false
INT, REAL, FLOAT	JSON number	42 → 42, 3.14 → 3.14
BIGINT, NUMERIC	JSON string	123456789 → "123456789"
DATE	ISO date string	'1998-01-01'::DATE → "1998-01-01"
TIMESTAMP, TIMESTAMPNTZ	ISO datetime string	'1998-01-01'::TIMESTAMP → "1998-01-01 00:00:00"
GEOGRAPHY	WKB hex string	Geography point → "0101000020E610000..."
NULL	JSON null	null → null

Behavior

• Struct conversion: Struct field names become JSON object keys. If a struct has unnamed fields, they are automatically named f1, f2, f3, etc.
• Nested structures: Nested structs become nested JSON objects, and arrays become JSON arrays.
• NULL handling: NULL input returns NULL. NULL fields within structs become JSON null values.
• Escaping: The function uses official json escaping that includes forward slashes (/ becomes \/).

Examples

The following example converts a simple struct to JSON:

SELECT TO_JSON_STRING(STRUCT(true, 1, 'x', null)) AS js;

js text
{“f1”:true,“f2”:1,“f3”:“x”,“f4”:null}

Rows: 1Execution time: 8.89ms

The following example converts a named struct to JSON:

SELECT TO_JSON_STRING(STRUCT(30, 'text')::STRUCT(id INT, desc TEXT)) AS js;

js text
{“id”:30,“desc”:“text”}

Rows: 1Execution time: 9.15ms

The following example converts an object literal to JSON:

SELECT TO_JSON_STRING({'id': 30, 'desc': 'text'}) AS js;

js text
{“id”:30,“desc”:“text”}

Rows: 1Execution time: 5.43ms

The following example demonstrates decimal conversion to string:

SELECT TO_JSON_STRING(STRUCT(30.45, 'text')::STRUCT(id DECIMAL, desc TEXT)) AS js;

js text
{“id”:30.450000000,“desc”:“text”}

Rows: 1Execution time: 6.32ms

The following example converts a complex nested structure with arrays:

SELECT TO_JSON_STRING({
  'id': 1,
  'name': { 'first': 'John', 'last': 'Doe' },
  'addresses': [
    {
      'street_name': 'Jane St.',
      'street_no': 42,
      'city': 'New York'
    },
    {
      'street_name': 'King St.',
      'street_no': 17,
      'city': 'Los Angeles'
    }
  ]
}) AS js;

js text
{“id”:1,“name”:{“first”:“John”,“last”:“Doe”},“addresses”:[{“street_name”:“Jane St.”,“street_no”:42,“city”:“New York”},{“street_name”:“King St.”,“street_no”:17,“city”:“Los Angeles”}]}

Rows: 1Execution time: 15.93ms

The following example demonstrates special character escaping:

SELECT TO_JSON_STRING({'~a': 1, '/b\': 2}) AS js;

js text
{“~a”:1,“/b\“:2}

Rows: 1Execution time: 5.01ms

The following example shows NULL input handling:

SELECT TO_JSON_STRING(NULL) AS js;

js text null
NULL

Rows: 1Execution time: 6.48ms