> ## Documentation Index
> Fetch the complete documentation index at: https://docs.firebolt.io/llms.txt
> Use this file to discover all available pages before exploring further.

> Reference material for TO_JSON_STRING function

# 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

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
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](https://www.json.org/json-en.html) json escaping that includes forward slashes (`/` becomes `\/`).

## Examples

The following example converts a simple struct to JSON:

<div className="query-window">
  ```
  SELECT TO_JSON_STRING(STRUCT(true, 1, 'x', null)) AS js;
  ```

  | js <span>text</span>                   |
  | :------------------------------------- |
  | \{"f1":true,"f2":1,"f3":"x","f4":null} |

  <p><span>Rows: 1</span><span>Execution time: 8.89ms</span></p>
</div>

The following example converts a named struct to JSON:

<div className="query-window">
  ```
  SELECT TO_JSON_STRING(STRUCT(30, 'text')::STRUCT(id INT, desc TEXT)) AS js;
  ```

  | js <span>text</span>     |
  | :----------------------- |
  | \{"id":30,"desc":"text"} |

  <p><span>Rows: 1</span><span>Execution time: 9.15ms</span></p>
</div>

The following example converts an object literal to JSON:

<div className="query-window">
  ```
  SELECT TO_JSON_STRING({'id': 30, 'desc': 'text'}) AS js;
  ```

  | js <span>text</span>     |
  | :----------------------- |
  | \{"id":30,"desc":"text"} |

  <p><span>Rows: 1</span><span>Execution time: 5.43ms</span></p>
</div>

The following example demonstrates decimal conversion to string:

<div className="query-window">
  ```
  SELECT TO_JSON_STRING(STRUCT(30.45, 'text')::STRUCT(id DECIMAL, desc TEXT)) AS js;
  ```

  | js <span>text</span>               |
  | :--------------------------------- |
  | \{"id":30.450000000,"desc":"text"} |

  <p><span>Rows: 1</span><span>Execution time: 6.32ms</span></p>
</div>

The following example converts a complex nested structure with arrays:

<div className="query-window">
  ```
  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 <span>text</span>                                                                                                                                                                            |
  | :---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
  | \{"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"}]} |

  <p><span>Rows: 1</span><span>Execution time: 15.93ms</span></p>
</div>

The following example demonstrates special character escaping:

<div className="query-window">
  ```
  SELECT TO_JSON_STRING({'~a': 1, '/b\': 2}) AS js;
  ```

  | js <span>text</span> |
  | :------------------- |
  | \{"\~a":1,"/b\\":2}  |

  <p><span>Rows: 1</span><span>Execution time: 5.01ms</span></p>
</div>

The following example shows NULL input handling:

<div className="query-window">
  ```
  SELECT TO_JSON_STRING(NULL) AS js;
  ```

  | js <span>text null</span> |
  | :------------------------ |
  | NULL                      |

  <p><span>Rows: 1</span><span>Execution time: 6.48ms</span></p>
</div>