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

# JSON_POINTER_EXTRACT_TEXT

Accepts a JSON document and pointer expression. If the key exists and the value is a JSON string, `JSON_POINTER_EXTRACT_TEXT` returns it as SQL `TEXT`, removing outer quotes and decoding characters. Otherwise, it returns `NULL`.

## Syntax

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
JSON_POINTER_EXTRACT_TEXT
(<json>, <json_pointer_expression>)
```

## Parameters

| Parameter                   | Description                                                                                                                                                                                                            | Supported input types |
| :-------------------------- | :--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :-------------------- |
| `<json>`                    | The JSON document.                                                                                                                                                                                                     | `TEXT`                |
| `<json_pointer_expression>` | A JSON pointer expression to the location of the desired sub-document in the JSON. 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

**Example**

The following code example extracts the value at path `/value/uid` from the JSON document, removes the outermost quotes, and returns the result as SQL `TEXT`:

<div className="query-window">
  ```
  SELECT JSON_POINTER_EXTRACT_TEXT('{"value": {"uid": "987654"} }', '/value/uid') AS res;
  ```

  | res <span>text null</span> |
  | :------------------------- |
  | 987654                     |

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

**Example**

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

<div className="query-window">
  ```
  SELECT JSON_POINTER_EXTRACT_TEXT('{"value": {"uid": "987654"} }', '/value/no_such_key') AS res;
  ```

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

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

**Example**

The following code example attempts to extract the value at the path `/value/code` from the JSON document and returns `NULL` because the value at the specified path is an integer, not a string:

<div className="query-window">
  ```
  SELECT JSON_POINTER_EXTRACT_TEXT('{"value": {"code": 12} }', '/value/code') AS res;
  ```

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

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

**Example**

The following code example navigates to the third element at index `2` of the array at `/value/keywords` in the JSON document, removes the outermost quotes, and returns it as SQL `TEXT`:

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

  | res <span>text null</span> |
  | :------------------------- |
  | analytics                  |

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