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

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

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.	`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

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Aliases

Parameters

Return Type

Examples

​Syntax

​Aliases

​Parameters

​Return Type

​Examples

Syntax

Aliases

Parameters

Return Type

Examples