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 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: 
SELECT JSON_EXTRACT_ARRAY(<json_common_example>, '/value/dyid', 'JSONPointer')
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>: 
SELECT JSON_EXTRACT_ARRAY(<json_common_example>, '/value/no_such_key', 'JSONPointer')
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: 
SELECT JSON_POINTER_EXTRACT_ARRAY(<json_common_example>,'/value/keywords')
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: 
SELECT JSON_POINTER_EXTRACT_ARRAY(<json_common_example>,'/value/events')
Returns The previous code example returns the following SQL array, containing two text elements: 
{
        '{
            "EventId": 547,
            "EventProperties" :
            {
                "UserName":"John Doe",
                "Successful": true
            }
        }',
        '{
            "EventId": 548,
            "EventProperties" :
            {
                "ProductID":"xy123",
                "items": 2
            }
        }'
}
⌘I