JSON_POINTER_EXTRACT_TEXT

Reference material for JSON_POINTER_EXTRACT_TEXT function

Accepts a JSON document and pointer expression. If the key exists and the value is a JSON string, JSON_POINTER_EXTRACT_TEXT returns it as SQL TEXT, removing outer quotes and decoding characters. Otherwise, it returns NULL.

Syntax

JSON_POINTER_EXTRACT_TEXT
(<json>, <json_pointer_expression>)

Parameters

Parameter	Description	Supported input types
<json>	The JSON document.	TEXT
<json_pointer_expression>	A JSON pointer expression to the location of the desired sub-document in the JSON. For more information, see JSON pointer expression syntax.	TEXT

Return Type

TEXT

• If any input values are NULL, the function will return NULL.

Examples

Example

The following code example extracts the value at path /value/uid from the JSON document, removes the outermost quotes, and returns the result as SQL TEXT:

SELECT JSON_POINTER_EXTRACT_TEXT('{"value": {"uid": "987654"} }', '/value/uid') AS res;

res text null
987654

Rows: 1Execution time: 5.57ms

Example

The following code example attempts to extract the value at the path /value/no_such_key from the JSON document and returns NULL because the key does not exist:

SELECT JSON_POINTER_EXTRACT_TEXT('{"value": {"uid": "987654"} }', '/value/no_such_key') AS res;

res text null
NULL

Rows: 1Execution time: 7.32ms

Example

The following code example attempts to extract the value at the path /value/code from the JSON document and returns NULL because the value at the specified path is an integer, not a string:

SELECT JSON_POINTER_EXTRACT_TEXT('{"value": {"code": 12} }', '/value/code') AS res;

res text null
NULL

Rows: 1Execution time: 8.20ms

Example

The following code example navigates to the third element at index 2 of the array at /value/keywords in the JSON document, removes the outermost quotes, and returns it as SQL TEXT:

SELECT JSON_POINTER_EXTRACT_TEXT('{"key":123,"value":{"dyid":987,"uid":"987654","keywords":["insanely","fast","analytics"]}}', '/value/keywords/2') AS res;

res text null
analytics

Rows: 1Execution time: 4.60ms