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

# JSON_POINTER_EXTRACT_VALUES

Accepts a JSON document and pointer expression. If the key exists and holds a JSON object (map),
`JSON_POINTER_EXTRACT_VALUES` returns all the values in that object as SQL `ARRAY(TEXT)`, while the values remain raw as
they appeared in the original JSON document.
Otherwise, it returns `NULL`.

## Syntax

```sql theme={"theme":{"light":"css-variables","dark":"css-variables"}}
JSON_POINTER_EXTRACT_VALUES
(<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

`ARRAY(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 all the values at path `/value/events/0/EventProperties` from the JSON document, and
returns the raw values as SQL `ARRAY(TEXT)`:

<div className="query-window">
  ```
  SELECT JSON_POINTER_EXTRACT_VALUES('{"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}}]}}', '/value/events/0/EventProperties') AS res;
  ```

  | res <span>array(text) null</span> |
  | :-------------------------------- |
  | \['"John Doe"', 'true']           |

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

**Example**

The following code example attempts to extract the keys 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_VALUES('{"key":123,"value":{"dyid":987,"uid":"987654"}}', '/value/no_such_key') AS res;
  ```

  | res <span>array(text) null</span> |
  | :-------------------------------- |
  | NULL                              |

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

**Example**

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

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

  | res <span>array(text) null</span> |
  | :-------------------------------- |
  | NULL                              |

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