JSON_EXTRACT

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

Parameter	Description	Supported 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

Overview

Performance and Observability

Security

Self-Managed

Managed service

Guides

SQL reference

Release notes

API reference

Legal

Syntax

Aliases

Parameters

Return Type

Examples

​Syntax

​Aliases

​Parameters

​Return Type

​Examples

Syntax

Aliases

Parameters

Return Type

Examples