JSON_EXTRACT
Takes an expression containing a JSON document, a JSON pointer expression, and an expected data type parameter. If the key specified using the JSON pointer expression exists, and its type conforms with the expected data type parameter, JSON_EXTRACT
returns the value of the data type specified. Otherwise, returns NULL
.
Syntax
JSON_EXTRACT(<json>, '<json_pointer_expression>', '<expected_type>')
Parameter | Type | Description |
---|---|---|
<json> | TEXT | The JSON document from which the value is to be extracted. |
<json_pointer_expression> | Literal string | A JSON pointer to the location of the array in the JSON. For more information, see JSON pointer expression syntax. |
<expected_type> | Literal string | The expected data type of the key indicated by <json_pointer_expression> , such as TEXT or INTEGER . For more information, see supported type parameters. |
Example
For the JSON document indicated by <json_common_example>
below, see JSON common example. The Returns result is based on this example.
SELECT
JSON_EXTRACT(< json_common_example >, '/value/dyid', 'INTEGER')
Returns: 987
SELECT
JSON_EXTRACT(<json_common_example>, '/value/no_such_key', 'TEXT')
Returns: NULL
SELECT
JSON_EXTRACT(<json_common_example>, '/value/uid', 'INTEGER')
Returns: NULL
since the JSON type under that key is a string.
SELECT
JSON_EXTRACT(<json_common_example>,'/value/keywords', 'ARRAY(TEXT)')
Returns: ["insanely","fast","analytics"]