Accepts a JSON document and pointer expression. If the key exists and holds a JSON object (map),
JSON_POINTER_EXTRACT_VALUES returns all the values in that object as SQL ARRAY(TEXT), while the values remain raw as
they appeared in the original JSON document.
Otherwise, it returns NULL.
Syntax
JSON_POINTER_EXTRACT_VALUES
(<json>, <json_pointer_expression>)
Parameters
| Parameter | Description | Supported input types |
|---|
<json> | The JSON document. | TEXT |
<json_pointer_expression> | A JSON pointer expression to the location of the desired sub-document in the JSON. 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 example extracts all the values at path /value/events/0/EventProperties from the JSON document, and
returns the raw values as SQL ARRAY(TEXT):
SELECT JSON_POINTER_EXTRACT_VALUES('{"key":123,"value":{"dyid":987,"uid":"987654","keywords":["insanely","fast","analytics"],"tagIdToHits":{"map":{"1737729":32,"1775582":35}},"events":[{"EventId":547,"EventProperties":{"UserName":"John Doe","Successful":true}},{"EventId":548,"EventProperties":{"ProductID":"xy123","items":2}}]}}', '/value/events/0/EventProperties') AS res;
| res array(text) null |
|---|
| [‘“John Doe”’, ‘true’] |
Rows: 1Execution time: 6.29ms
/value/no_such_key from the JSON document and returns NULL because the key does not exist:
SELECT JSON_POINTER_EXTRACT_VALUES('{"key":123,"value":{"dyid":987,"uid":"987654"}}', '/value/no_such_key') AS res;
Rows: 1Execution time: 5.44ms
/value/keywords from the JSON document and returns NULL because the value at the specified path is an array, not an object:
SELECT JSON_POINTER_EXTRACT_VALUES('{"key":123,"value":{"keywords":["insanely","fast","analytics"]}}', '/value/keywords') AS res;
Rows: 1Execution time: 5.18ms
