View Source

Computes the 0-based index value for an array element in the specified column, array literal, or function that returns an array.

This function calculates based on the outer layer of an array. If your array is nested, the count of inner elements is not factored.
If a row contains a missing array, the returned value is 0. If it contains a value that is not recognized as an array, the returned value is null.

Array literal reference example:

derive type:single value:ARRAYELEMENTAT([A,B,C,D],2)

Output: Returns the third value in the array, which is C.

Column reference example:

derive type:single value:ARRAYELEMENTAT(myArrays,9) as:'10th_myArrays'

Output: Generates the new 10th_myArrays column containing the tenth element of the arrays listed in the myArrays column.

Array function example:

derive type:single value:ARRAYELEMENTAT(CONCAT([colA,colB]),3) as:'strArrayElementAtIndex'

Output: Generates the new strArrayElementAtIndex column containing the fourth element of the concatenated array.

derive type:single value:ARRAYELEMENTAT(array_ref,int_index_ref)

Argument	Required?	Data Type	Description
array_ref	Y	string	Name of Array column, Array literal, or function returning an Array to apply to the function
int_index_ref	Y	integer (non-negative)	Index value for the array element to return

Name of the array column, array literal, or function returning an array whose elements you want to return.

Required?	Data Type	Example Value
Yes	String (column reference or function) or array literal	`myArray1`

Non-negative integer value representing the index value of the array element to return.

Value must a non-negative integer. If the value is 0, then the first element of the array is returned.
If this value is greater than the length of the string, then a null value is returned.
References to columns of integer data type are not supported.

Usage Notes:

Required?	Data Type	Example Value
Yes	Integer (non-negative)	`5`