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

# JSON_EXTRACT_ARRAY

Accepts a JSON document, path expression, and optional path syntax. If the key exists and holds a JSON array, `JSON_EXTRACT_ARRAY` returns an SQL ARRAY(TEXT) with the array's elements as raw text, and otherwise returns `NULL`.

## Syntax

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

### Aliases

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

## Parameters

| Parameter                | Description                                                                                                                                                                                                                    | Supported input types |
| :----------------------- | :----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | :-------------------- |
| `<json>`                 | The JSON document.                                                                                                                                                                                                             | `TEXT`                |
| `<json_path_expression>` | A JSON path to the location of the desired element within the JSON document.                                                                                                                                                   | `TEXT`                |
| `<path_syntax>`          | The expected syntax of the `<json_path_expression>` currently only supports 'JSONPointer'. 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 extracts the value at the path `/value/dyid` from the JSON document and returns `NULL` because the specified path does not reference an array:

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

  | json\_extract\_array <span>array(text) null</span> |
  | :------------------------------------------------- |
  | NULL                                               |

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

**Example**

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

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

  | json\_extract\_array <span>array(text) null</span> |
  | :------------------------------------------------- |
  | NULL                                               |

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

**Example**

The following code example extracts the array at the path `/value/keywords` from the JSON document using the JSON pointer syntax:

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

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

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

**Example**

The following code example extracts the array located at the path `/value/events` from the JSON document using the JSON pointer syntax:

<div className="query-window">
  ```
  SELECT JSON_POINTER_EXTRACT_ARRAY('{"key":123,"value":{"events":[{"EventId":547,"EventProperties":{"UserName":"John Doe","Successful":true}},{"EventId":548,"EventProperties":{"ProductID":"xy123","items":2}}]}}', '/value/events');
  ```

  | json\_pointer\_extract\_array <span>array(text) null</span>                                                                                               |
  | :-------------------------------------------------------------------------------------------------------------------------------------------------------- |
  | \['\{"EventId":547,"EventProperties":\{"UserName":"John Doe","Successful":true}}', '\{"EventId":548,"EventProperties":\{"ProductID":"xy123","items":2}}'] |

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