Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

JSON_EXTRACT_PATH_TEXT Function

JSON_EXTRACT_PATH_TEXT returns the value for the key:value pair referenced by a series of path elements in a JSON string. The JSON path can be nested up to five levels deep. Path elements are case-sensitive. If a path element does not exist in the JSON string, JSON_EXTRACT_PATH_TEXT returns an empty string.

For more information, see JSON Functions.

Syntax

Copy
json_extract_path_text('json_string', 'path_elem' [,'path_elem'[, …]])

Arguments

json_string

A properly formatted JSON string.

path_elem

A path element in a JSON string. One path element is required. Additional path elements can be specified, up to five levels deep.

In a JSON string, Amazon Redshift recognizes \n as a newline character and \t as a tab character. To load a backslash, escape it with a backslash ( \\ ). For more information, see Escape Characters in JSON.

Return Type

VARCHAR string representing the JSON value referenced by the path elements.

Example

The following example returns the value for the path 'f4', 'f6':

Copy
select json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"star"}}','f4', 'f6'); json_extract_path_text ---------------------- star