> ## 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 JSON_EXTRACT function

# JSON_EXTRACT

Accepts an expression containing a JSON document, a JSON path expression, and an optional path syntax. If the key
specified in the JSON path exists, `JSON_EXTRACT` returns the sub-JSON document at the specified path, and otherwise, returns `NULL`.

## Syntax

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
JSON_EXTRACT
(<json>, <json_path_expression>, path_syntax => <path_syntax>)
```

### Aliases

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
JSON_POINTER_EXTRACT
(<json>, <json_path_expression>) ->
JSON_EXTRACT(<json>, <json_path_expression>, path_syntax => 'JSONPointer')
```

## Parameters

| Parameter                | Description                                                                                                                                                                                                                                | Supported input types |
| :----------------------- | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :-------------------- |
| `<json>`                 | The JSON document.                                                                                                                                                                                                                         | `TEXT`                |
| `<json_path_expression>` | A JSON path that specifies the location of the desired sub-document within the JSON document.                                                                                                                                              | `TEXT`                |
| `<path_syntax>`          | The expected syntax of the `<json_path_expression>`, currently supports only the 'JSONPointer' syntax. For more information, see [JSON pointer expression syntax](/reference-sql/functions-reference/json#json-pointer-expression-syntax). | `TEXT`                |

## Return Type

`TEXT`

* If any input values are `NULL`, the function will return `NULL`.

## Examples

For the JSON document used in the examples below, see [JSON common example](/reference-sql/functions-reference/json#json-common-example).
**Example**

The following code example extracts the value at the path `/value/dyid` from the JSON document using the `JSONPointer` syntax and returns `"987"`:

<div className="query-window">
  ```
  SELECT JSON_EXTRACT('{"key":123,"value":{"dyid":987,"uid":"987654","keywords":["insanely","fast","analytics"]}}', '/value/dyid', 'JSONPointer');
  ```

  | json\_extract <span>text null</span> |
  | :----------------------------------- |
  | 987                                  |

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

**Example**

The following code example attempts to extract a value from the path `/value/no_such_key` and returns `NULL` because the key does not exist:

<div className="query-window">
  ```
  SELECT JSON_EXTRACT('{"key":123,"value":{"dyid":987}}', '/value/no_such_key', 'JSONPointer');
  ```

  | json\_extract <span>text null</span> |
  | :----------------------------------- |
  | NULL                                 |

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

**Example**

The following code example extracts the value at the path `/value/uid` using the `JSON_POINTER_EXTRACT` alias and returns `'"987654"'` with double quotes, indicating it is a JSON string:

<div className="query-window">
  ```
  SELECT JSON_POINTER_EXTRACT('{"key":123,"value":{"uid":"987654"}}', '/value/uid');
  ```

  | json\_pointer\_extract <span>text null</span> |
  | :-------------------------------------------- |
  | "987654"                                      |

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

**Example**

The following code example extracts the array at the path `/value/keywords` using the `JSON_POINTER_EXTRACT` alias:

<div className="query-window">
  ```
  SELECT JSON_POINTER_EXTRACT('{"value":{"keywords":["insanely","fast","analytics"]}}', '/value/keywords');
  ```

  | json\_pointer\_extract <span>text null</span> |
  | :-------------------------------------------- |
  | \["insanely","fast","analytics"]              |

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

**Example**

The following code example extracts the third element at index `2` from the keywords array using zero-based indexing:

<div className="query-window">
  ```
  SELECT JSON_POINTER_EXTRACT('{"value":{"keywords":["insanely","fast","analytics"]}}', '/value/keywords/2');
  ```

  | json\_pointer\_extract <span>text null</span> |
  | :-------------------------------------------- |
  | "analytics"                                   |

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