ARRAY_ELEMENT() — Returns the element at the specified location in a JSON array.
ARRAY_ELEMENT( JSON-array, element-position )
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":
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:
Finally, the element may itself be a valid JSON-encoded object. For example, the following function returns the string "[0,1,2,3]":
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 position argument is less than zero
The position argument is greater than or equal to the length of the array
The JSON string does not represent an array (that is, the string is a valid JSON scalar value or object)
The function returns an error if the first argument is not a valid JSON string.
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".