Skip to main content
Accepts an expression containing a JSON document, a JSON path expression, and an optional path syntax. If the key specified in the JSON path exists, JSON_EXTRACT returns the sub-JSON document at the specified path, and 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

ParameterDescriptionSupported input types
<json>The JSON document.TEXT
<json_path_expression>A JSON path that specifies the location of the desired sub-document within the JSON document.TEXT
<path_syntax>The expected syntax of the <json_path_expression>, currently supports only the ‘JSONPointer’ syntax. For more information, see JSON pointer expression syntax.TEXT

​
Return Type

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 the value at the path /value/dyid from the JSON document using the JSONPointer syntax and returns "987": 
SELECT JSON_EXTRACT('{"key":123,"value":{"dyid":987,"uid":"987654","keywords":["insanely","fast","analytics"]}}', '/value/dyid', 'JSONPointer');
json_extract text null
987

Rows: 1Execution time: 4.86ms

Example The following code example attempts to extract a value from the path /value/no_such_key and returns NULL because the key does not exist: 
SELECT JSON_EXTRACT('{"key":123,"value":{"dyid":987}}', '/value/no_such_key', 'JSONPointer');
json_extract text null
NULL

Rows: 1Execution time: 4.98ms

Example The following code example extracts the value at the path /value/uid using the JSON_POINTER_EXTRACT alias and returns '"987654"' with double quotes, indicating it is a JSON string: 
SELECT JSON_POINTER_EXTRACT('{"key":123,"value":{"uid":"987654"}}', '/value/uid');
json_pointer_extract text null
”987654”

Rows: 1Execution time: 8.19ms

Example The following code example extracts the array at the path /value/keywords using the JSON_POINTER_EXTRACT alias: 
SELECT JSON_POINTER_EXTRACT('{"value":{"keywords":["insanely","fast","analytics"]}}', '/value/keywords');
json_pointer_extract text null
[“insanely”,“fast”,“analytics”]

Rows: 1Execution time: 6.28ms

Example The following code example extracts the third element at index 2 from the keywords array using zero-based indexing: 
SELECT JSON_POINTER_EXTRACT('{"value":{"keywords":["insanely","fast","analytics"]}}', '/value/keywords/2');
json_pointer_extract text null
”analytics”

Rows: 1Execution time: 5.55ms