JSON_EXTRACT_ARRAY
Reference material for JSON_EXTRACT_ARRAY function
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
Aliases
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 returnNULL
.
Examples
For the JSON document indicated by <json_common_example>
below,
see JSON common example. The returned result is based on the following example.
Example
The following code extracts the value at the path value/dyid
from the JSON document represented by <json_common_example>
, and returns it as an SQL array using the JSONPointer
syntax:
Returns
The previous example returns NULL
because the specified path does not reference an array.
Example
The following code example attempts to extract an array from a path /value/no_such_key
in the JSON document represented by <json_common_example>
:
Returns
The previous code example returns NULL
because the key does not exist.
Example
The following code example extracts the array at the path /value/keywords
from the JSON document represented by <json_common_example>
using the JSON pointer syntax:
Returns
The previous code example returns the following: {"insanely", "fast", "analytics"}
.
Example
The following code example extracts the array located at the path /value/events
from the JSON document represented by <json_common_example>
using the JSON pointer syntax:
Returns
The previous code example returns the following SQL array, containing two text elements: