Description

The ARRAY_ELEMENT() function extracts a single element from a JSON array. The array position is zero-based. In other words, the first element in the array is in position "0". The function returns the element as a string. For example, the following function invocation returns the string "two":

ARRAY_ELEMENT('["zero","one","two","three"]',2)

Note that the array element is always returned as a string. So in the following example, the function returns "2" as a string rather than an integer:

ARRAY_ELEMENT('[0,1,2,3]',2)

Finally, the element may itself be a valid JSON-encoded object. For example, the following function returns the string "[0,1,2,3]":

ARRAY_ELEMENT('[[0,1,2,3],["zero","one","two","three"]]',0)

The ARRAY_ELEMENT() function can be combined with other functions, such as FIELD(), to traverse more complex JSON structures. The function returns a NULL value if any of the following conditions are true:

The function returns an error if the first argument is not a valid JSON string.

Example

The following example uses the ARRAY_ELEMENT() function along with FIELD() to extract specific array elements from one field in a JSON-encoded VARCHAR column:

SELECT language, 
       ARRAY_ELEMENT(FIELD(words,'colors'),1) AS color,
       ARRAY_ELEMENT(FIELD(words,'numbers'),2) AS number
       FROM world_languages WHERE language = 'French'; 

Assuming the column words has the following structure, the query returns the strings "French', "vert", and "trois".

{"colors":["rouge","vert","bleu"],
 "numbers":["un","deux","trois"]}