Accepts a JSON document and pointer expression. If the key exists and holds a JSON object (map),
JSON_POINTER_EXTRACT_KEYS returns all the keys in that object as SQL ARRAY(TEXT), removing outer quotes and decoding
characters. Otherwise, it returns NULL.
Syntax
JSON_POINTER_EXTRACT_KEYS
(<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 keys at path /value from the JSON document, removes the outermost quotes,
and returns the result as SQL ARRAY(TEXT):
SELECT JSON_POINTER_EXTRACT_KEYS('{"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') AS res;
| res array(text) null |
|---|
| [‘dyid’, ‘uid’, ‘keywords’, ‘tagIdToHits’, ‘events’] |
Rows: 1Execution time: 5.31ms
/value/no_such_key from the JSON document and returns NULL because the key does not exist:
SELECT JSON_POINTER_EXTRACT_KEYS('{"key":123,"value":{"dyid":987,"uid":"987654","keywords":["insanely","fast","analytics"]}}', '/value/no_such_key') AS res;
Rows: 1Execution time: 5.38ms
/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_KEYS('{"key":123,"value":{"keywords":["insanely","fast","analytics"]}}', '/value/keywords') AS res;
Rows: 1Execution time: 5.57ms
