JSON_EXTRACT_ARRAY_ELEMENT_TEXT function - Amazon Redshift
SyntaxArgumentsReturn typeExamples
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

JSON_EXTRACT_ARRAY_ELEMENT_TEXT function

Note

JSON_PARSE and its associated functions parse JSON values as SUPER, which Amazon Redshift parses more efficiently than VARCHAR.

Instead of using JSON_EXTRACT_ARRAY_ELEMENT_TEXT, we recommend that you parse your JSON strings using the JSON_PARSE function to get a SUPER value. Then, query the element you want using its array index, using the value[element position] syntax. For more information on querying array elements in SUPER values, go to Querying semistructured data.

The JSON_EXTRACT_ARRAY_ELEMENT_TEXT function returns a JSON array element in the outermost array of a JSON string, using a zero-based index. The first element in an array is at position 0. If the index is negative or out of bound, JSON_EXTRACT_ARRAY_ELEMENT_TEXT returns empty string. If the null_if_invalid argument is set to true and the JSON string is invalid, the function returns NULL instead of returning an error.

For more information, see JSON functions.

Syntax

JSON_EXTRACT_ARRAY_ELEMENT_TEXT('json string', pos [, null_if_invalid ] )

Arguments

json_string

A properly formatted JSON string.

pos

An INTEGER representing the index of the array element to be returned, using a zero-based array index.

null_if_invalid

(Optional) A BOOLEAN value that specifies whether to return NULL if the input JSON string is invalid instead of returning an error. To return NULL if the JSON is invalid, specify true (t). To return an error if the JSON is invalid, specify false (f). The default is false.

Return type

VARCHAR

A VARCHAR string representing the JSON array element referenced by pos.

Examples

To return array element at position 2, which is the third element of a zero-based array index, use the following example. 

SELECT JSON_EXTRACT_ARRAY_ELEMENT_TEXT('[111,112,113]', 2);
 
+---------------------------------+
| json_extract_array_element_text |
+---------------------------------+
|                             113 |
+---------------------------------+

To return an error because the JSON is invalid, use the following example.

SELECT JSON_EXTRACT_ARRAY_ELEMENT_TEXT('["a",["b",1,["c",2,3,null,]]]',1);
 
ERROR: invalid json array object ["a",["b",1,["c",2,3,null,]]]

To set null_if_invalid to true, so the statement returns NULL instead of returning an error for invalid JSON, use the following example.

SELECT JSON_EXTRACT_ARRAY_ELEMENT_TEXT('["a",["b",1,["c",2,3,null,]]]',1,true);
 
+---------------------------------+
| json_extract_array_element_text |
+---------------------------------+
| NULL                            |
+---------------------------------+