JSON_EXTRACT_ARRAY

Takes an expression containing a JSON document, a JSON path expression, and an optional path syntax. If the key specified using the JSON path expression exists and its value is a JSON array, JSON_EXTRACT_ARRAY returns SQL ARRAY(TEXT) contains all JSON elements as raw text inside the JSON array pointed by the given JSON path. 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 desiered sub-document in the JSON.	TEXT
<path_syntax>	The expected syntax of the <json_path_expression>, supports only ‘JSONPointer’ at the moment. For more information, see JSON pointer expression syntax.	TEXT

Return Type

ARRAY(TEXT)

• If any of the inputs is NULL the output is NULL (propagates nulls).

Example

For the JSON document indicated by <json_common_example> below, see JSON common example. The returned result is based on this example.

SELECT JSON_EXTRACT_ARRAY(<json_common_example>, '/value/dyid', 'JSONPointer')

Returns: NULL because it does not point to an array.

SELECT JSON_EXTRACT_ARRAY(<json_common_example>, '/value/no_such_key', 'JSONPointer')

Returns: NULL because the key does not exist.

SELECT JSON_POINTER_EXTRACT_ARRAY(<json_common_example>,'/value/keywords')

Returns: {"insanely", "fast", "analytics"}

SELECT JSON_POINTER_EXTRACT_ARRAY(<json_common_example>,'/value/events')

Returns (SQL array of 2 text elements):

{
        '{
            "EventId": 547,
            "EventProperties" :
            {
                "UserName":"John Doe",
                "Successful": true
            }
        }',
        '{
            "EventId": 548,
            "EventProperties" :
            {
                "ProductID":"xy123",
                "items": 2
            }
        }'
}