TO_JSON_STRING

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

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Parameters

Return Type

Type Conversions

Behavior

Examples

​Syntax

​Parameters

​Return Type

​Type Conversions

​Behavior

​Examples

Syntax

Parameters

Return Type

Type Conversions

Behavior

Examples