Skip to main content
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

JSON_EXTRACT_ARRAY
(<json>, <json_path_expression>, path_syntax => <path_syntax>)

​
Aliases

JSON_POINTER_EXTRACT_ARRAY
(<json>, <json_path_expression>) ->
JSON_EXTRACT_ARRAY(<json>, <json_path_expression>, path_syntax => 'JSONPointer')

​
Parameters

ParameterDescriptionSupported 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.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. 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: 
SELECT JSON_EXTRACT_ARRAY('{"key":123,"value":{"dyid":987,"uid":"987654","keywords":["insanely","fast","analytics"]}}', '/value/dyid', 'JSONPointer');
json_extract_array array(text) null
NULL

Rows: 1Execution time: 5.28ms

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: 
SELECT JSON_EXTRACT_ARRAY('{"key":123,"value":{"dyid":987,"keywords":["insanely","fast","analytics"]}}', '/value/no_such_key', 'JSONPointer');
json_extract_array array(text) null
NULL

Rows: 1Execution time: 5.13ms

Example The following code example extracts the array at the path /value/keywords from the JSON document using the JSON pointer syntax: 
SELECT JSON_POINTER_EXTRACT_ARRAY('{"key":123,"value":{"keywords":["insanely","fast","analytics"]}}', '/value/keywords');
json_pointer_extract_array array(text) null
[‘“insanely”’, ‘“fast”’, ‘“analytics”‘]

Rows: 1Execution time: 5.21ms

Example The following code example extracts the array located at the path /value/events from the JSON document using the JSON pointer syntax: 
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 array(text) null
[’{“EventId”:547,“EventProperties”:{“UserName”:“John Doe”,“Successful”:true}}’, ’{“EventId”:548,“EventProperties”:{“ProductID”:“xy123”,“items”:2}}‘]

Rows: 1Execution time: 5.65ms