JSON_EXTRACT

Takes an expression containing a JSON document, a JSON path expression, and an optional path syntax. If the key specified using the JSON path expression exists, JSON_EXTRACT returns the sub JSON document pointed by the given JSON path. Otherwise, returns NULL.

Syntax

JSON_EXTRACT(<json>, <json_path_expression>, path_syntax => <path_syntax>)

Aliases

JSON_POINTER_EXTRACT(<json>, <json_path_expression>) ->
JSON_EXTRACT(<json>, <json_path_expression>, path_syntax => 'JSONPointer')

Parameters

Parameter	Description	Supported input types
<json>	The JSON document.	TEXT
<json_path_expression>	A JSON path to the location of the desiered sub-document in the JSON.	TEXT
<path_syntax>	The expected syntax of the <json_path_expression>, supports only ‘JSONPointer’ at the moment. For more information, see JSON pointer expression syntax.	TEXT

Return Type

TEXT

• If any of the input is NULL the output is NULL (propagate nulls).

Example

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

SELECT JSON_EXTRACT(<json_common_example>, '/value/dyid', 'JSONPointer')

Returns: '987'

SELECT JSON_EXTRACT(<json_common_example>, '/value/no_such_key', 'JSONPointer')

Returns: NULL

SELECT JSON_POINTER_EXTRACT(<json_common_example>, '/value/uid')

Returns: '"987654"'

SELECT JSON_POINTER_EXTRACT(<json_common_example>,'/value/keywords')

Returns: '["insanely","fast","analytics"]'

SELECT JSON_POINTER_EXTRACT(<json_common_example>,'/value/keywords/2')

Returns: '"analytics"'