> ## 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 the TO_JSON function

# TO_JSON

Converts arbitrary values to `JSON` values, without parsing any JSON strings in the input. Arrays and structs are converted recursively into JSON arrays and JSON objects, respectively. Scalar values other than `BOOLEAN`, `INTEGER`, `BIGINT`, `REAL`, `DOUBLE`, or `TEXT` are converted to `TEXT` using the regular `CAST` function.

## Syntax

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
TO_JSON(<value>)
```

## Parameters

| Parameter | Description                                 | Supported Input Types |
| :-------- | :------------------------------------------ | :-------------------- |
| `<value>` | The value that should be converted to JSON. | Any                   |

## Return Type

`JSON`

* If `<value>` is `NULL` the return value is SQL `NULL`.
* If `<value>` is an array or struct containing nested SQL `NULL` values, these nested SQL `NULL`s are converted to JSON `null`s.

## Examples

**Example**

The following query converts a `STRUCT` value containing nested `STRUCT`, `ARRAY`, and scalar values to JSON. All of the scalar data types involved are natively representable in JSON.

<div className="query-window">
  ```
  SELECT TO_JSON({'a':[{'b':{'message':'hello world!','42':'the answer'}}]});
  ```

  | to\_json <span>json</span>                                          |
  | :------------------------------------------------------------------ |
  | \{'a': \[\{'b': \{'42': 'the answer', 'message': 'hello world!'}}]} |

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

**Example**

The following query converts a `STRUCT` value containing nested `STRUCT`, `ARRAY`, and `NULL` values to JSON.

<div className="query-window">
  ```
  SELECT TO_JSON({'a':[{'b':{'message':NULL}}]});
  ```

  | to\_json <span>json</span>            |
  | :------------------------------------ |
  | \{'a': \[\{'b': \{'message': NULL}}]} |

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

**Example**

The following query converts a plain SQL `NULL` value to JSON. Since the value is not nested within an `ARRAY` or `STRUCT`, it remains a SQL `NULL` value.

<div className="query-window">
  ```
  SELECT TO_JSON(NULL);
  ```

  | to\_json <span>json null</span> |
  | :------------------------------ |
  | NULL                            |

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

**Example**

The following query converts a `TIMESTAMP` value to JSON. Since `TIMESTAMP` values are not natively representable in JSON, they are converted to `TEXT` and represented as JSON strings.

<div className="query-window">
  ```
  SELECT TO_JSON('2026-01-01T00:00:00'::TIMESTAMP);
  ```

  | to\_json <span>json</span> |
  | :------------------------- |
  | 2026-01-01 00:00:00        |

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

**Example**

The following query converts a `TEXT` value containing valid JSON data to JSON. Note that the return value simply consists of a single JSON string though, i.e. the `TO_JSON` function does not parse JSON data contained in strings.

<div className="query-window">
  ```
  SELECT TO_JSON('{"a":"b"}');
  ```

  | to\_json <span>json</span> |
  | :------------------------- |
  | \{"a":"b"}                 |

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