Link Search Menu Expand Document

JSON_EXTRACT_ARRAY_RAW

Returns a string representation of a JSON array pointed by the supplied JSON pointer. The returned string represents a Firebolt array with elements that are string representations of the scalars or objects contained in the JSON array under the specified key, if the key exists. If the key does not exist, the function returns an empty array.

This function is useful when working with heterogeneously typed arrays and arrays containing JSON objects in which case each object will be further processed by functions such as TRANSFORM.

Syntax

JSON_EXTRACT_ARRAY_RAW(<json>, '<json_pointer_expression>')

Parameters

Parameter Description Supported input types
<json> The JSON document from which the array is to be extracted. TEXT
<json_pointer_expression> A JSON pointer to the location of the array in the JSON. For more information, see JSON pointer expression syntax. TEXT

Return Types

  • If the key exists, returns a string representation of a JSON ARRAY
  • If the key does not exist, returns an empty ARRAY

Example

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

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

Returns: ["{\"EventId\":547,\"EventProperties\":{\"UserName\":\"John Doe\",\"Successful\":true}}","{\"EventId\":548,\"EventProperties\":{\"ProductID\":\"xy123\",\"items\":2}}"]