This page has not been translated into your language. Request translation

JSON_EXTRACT_ARRAY_ELEMENT_TEXT function

PDFRSS

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 bounds, JSON_EXTRACT_ARRAY_ELEMENT_TEXT returns NULL. 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                            |
+---------------------------------+

Consider the following example statements. If the provided JSON string or the index is NULL, JSON_EXTRACT_ARRAY_ELEMENT_TEXT returns NULL regardless of the value of any other parameters.

--Statement where json_string is NULL.
SELECT json_extract_array_element_text(NULL, 0)

 json_extract_array_element_text
---------------------------------
                            NULL

--Statement where pos is NULL and json_string is invalid JSON.
SELECT json_extract_array_element_text('invalid_json', NULL);

 json_extract_array_element_text
---------------------------------
                            NULL

--Statement where json_string is NULL and null_if_invalid is FALSE.
SELECT json_extract_array_element_text(NULL, 0, FALSE);

 json_extract_array_element_text
---------------------------------
                            NULL

Consider the following example statements. When null_if_invalid is TRUE, JSON_EXTRACT_ARRAY_ELEMENT_TEXT returns NULL when json_string is invalid JSON. If null_if_invalid is FALSE or isn’t set, the function returns an error when json_string is invalid.

--Statement with invalid JSON where null_if_invalid is TRUE.
SELECT json_extract_array_element_text('invalid_json', 0, TRUE);

 json_extract_array_element_text
---------------------------------
                            NULL
                            
--Statement with invalid JSON where null_if_invalid is FALSE.
SELECT json_extract_array_element_text('invalid_json', 0);

ERROR:  JSON parsing error

Consider the following example, where json_string is valid JSON, and pos refers to a JSON null value. In this case, JSON_EXTRACT_ARRAY_ELEMENT_TEXT returns NULL, regardless of the value of null_if_invalid.

--Statement selecting a null value.
SELECT json_extract_array_element_text('[null]', 0);

  json_extract_array_element_text 
----------------------------------
                             NULL