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>', '<data_type>')

Parameters

Parameter	Description	Supported input types
`<json>`	The JSON document from which the value 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`
`<data_type>`	The expected data type of the key indicated by `<json_pointer_expression>`, such as `TEXT` or `INTEGER`. For more information, see supported type parameters.	Any data type

Return Types

If key is provided, returns the value of the data type specified
If no key is provided, returns NULL

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"]