> ## 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.

<AgentInstructions>

## Submitting Feedback

If you encounter incorrect, outdated, or confusing documentation on this page, submit feedback:

POST https://docs.firebolt.io/feedback

```json
{
  "path": "/reference-sql/functions-reference/json",
  "feedback": "Description of the issue"
}
```

Only submit feedback when you have something specific and actionable to report.

</AgentInstructions>

> Reference for JSON functions

# JSON functions

# JSON Functions Operating on JSON Data

These JSON functions provide means to process JSON documents stored in native `JSON` columns. This should be preferred over storing JSON data in `TEXT` columns.

1. These operators extract a field from a JSON value, returing another JSON value:
   * [Dot Operator](/reference-sql/functions-reference/json/json-operators#dot-operator) (`.`)
   * [Subscript Operator](/reference-sql/functions-reference/json/json-operators#subscript-operator-\[]) (`[]`)
2. These functions convert a JSON value to a SQL value:
   * [JSON\_VALUE](/reference-sql/functions-reference/json/json-value)
3. These functions convert a SQL value to a JSON value:
   * [TO\_JSON](/reference-sql/functions-reference/json/to-json)

# JSON Functions Operating on TEXT Data

These JSON functions provide means to process JSON documents stored in `TEXT` columns.
There are three types of JSON functions for extracting values:

1. These functions extract part of the document, but preserve the original data in the result:
   * [JSON\_EXTRACT](/reference-sql/functions-reference/json/json-extract)
   * [JSON\_EXTRACT\_ARRAY](/reference-sql/functions-reference/json/json-extract-array)
   * [JSON\_POINTER\_EXTRACT\_VALUES](/reference-sql/functions-reference/json/json-pointer-extract-values)
2. These functions convert a JSON value to a SQL value:
   * [JSON\_VALUE](/reference-sql/functions-reference/json/json-value)
   * [JSON\_VALUE\_ARRAY](/reference-sql/functions-reference/json/json-value-array)
   * [JSON\_POINTER\_EXTRACT\_KEYS](/reference-sql/functions-reference/json/json-pointer-extract-keys)
3. This function extracts a JSON value and converts it to a SQL value, combining the functionalities of the previous two types of functions:
   * [JSON\_POINTER\_EXTRACT\_TEXT](/reference-sql/functions-reference/json/json-pointer-extract-text)

The first two types of functions can be used in conjunction with each other to extract and convert JSON values to SQL values.
The following code uses `JSON_POINTER_EXTRACT` to extract a value from a JSON document and `JSON_VALUE` to convert it into a SQL value:

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
SELECT JSON_VALUE(JSON_POINTER_EXTRACT('{"key1":{"key3":"val1" }, "key2":"val2", "key3":5}', '/key1/key3'));
```

This statement returns `val1` as a SQL `TEXT` value, rather than the original JSON value `"val1"` with the double quotes.

The [`JSON_FORMAT`](/reference-sql/functions-reference/json/json-format) function can be used to format JSON documents, including pretty-printing and minifying documents.

Currently, JSON functions support only JSON pointer expressions, which provide a method for accessing specific elements within a JSON document.
For convenience, the following aliases are available for JSON functions that use JSON pointer expressions:

* `JSON_POINTER_EXTRACT` is an alias for `JSON_EXTRACT` with the `path_syntax` parameter set to `'JSONPointer'`.
* `JSON_POINTER_EXTRACT_ARRAY` is an alias for `JSON_EXTRACT_ARRAY` with the `path_syntax` parameter set to `'JSONPointer'`.

Other than JSON functions, we also support some [JSON operators](/reference-sql/functions-reference/json/json-operators).

### JSON pointer expression syntax

The placeholder `<json_pointer_expression>` indicates where you should use a JSON pointer, which allows access to specific elements in a JSON document. For a formal specification, see [RFC6901](https://tools.ietf.org/html/rfc6901).

A JSON pointer begins with a forward slash (`/`), indicating the root of the JSON document, followed by a sequence of property (key) names or zero-based ordinal numbers separated by slashes. Property names identify specific keys, while index numbers specify the Nth element in an array or object.

The tilde (`~`) and forward slash (`/`) characters have special meanings and need to be escaped as follows:

* To specify a literal tilde (`~`), use `~0`.
* To specify a literal forward slash (`/`), use `~1`.

For example, consider the following JSON document:

```javascript theme={"theme":{"light":"github-light","dark":"github-dark"}}
{
    "key": 123,
    "key~with~tilde": 2,
    "key/with/slash": 3,
    "value": {
      "dyid": 987,
      "keywords" : ["insanely","fast","analytics"]
    }
}
```

In the previous JSON document, the JSON pointer expressions evaluate to the following:

| Pointer             | Result                                                                                                                                                                                                              | Notes                                                                                                                               |
| :------------------ | :------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | :---------------------------------------------------------------------------------------------------------------------------------- |
| `/`                 | `{` <br />`   "key": 123,` <br />`   "key~with~tilde": 2,` <br />`   "key/with/slash": 3,` <br />`   "value": {` <br />`      "dyid": 987,` <br />`      "keywords" : ["insanely","fast","analytics"]` <br />`   }` | Returns the whole document.                                                                                                         |
| `/key`              | 123                                                                                                                                                                                                                 |                                                                                                                                     |
| `/key~0with~0tilde` | 2                                                                                                                                                                                                                   | Indicates the value associated with the `key~with~tilde` property name.                                                             |
| `/key~1with~1slash` | 3                                                                                                                                                                                                                   | Indicates the value associated with the `key/with/slash` property name.                                                             |
| `/0`                | 123                                                                                                                                                                                                                 | Uses an ordinal to indicate the value associated with the `key` property name. The `key` property is in the first 0-based position. |
| `/value/keywords/2` | analytics                                                                                                                                                                                                           | Indicates the element "analytics", which is in the third 0-based position of the array value associated with the keywords property. |

### JSON common example

The following JSON document, represented by the `<json_common_example>` placeholder, is used as a basis for all JSON function examples in this reference.

```json theme={"theme":{"light":"github-light","dark":"github-dark"}}
{
    "key": 123,
    "value": {
      "dyid": 987,
      "uid": "987654",
      "keywords" : ["insanely","fast","analytics"],
      "tagIdToHits": {
        "map": {
          "1737729": 32,
          "1775582": 35
        }
      },
      "events":[
        {
            "EventId": 547,
            "EventProperties" :
            {
                "UserName":"John Doe",
                "Successful": true
            }
        },
        {
            "EventId": 548,
            "EventProperties" :
            {
                "ProductID":"xy123",
                "items": 2
            }
        }
      ]
    }
}
```