ELEMENT_AT
Retrieves the element of an array at a given index.
Syntax
ELEMENT_AT(ARRAY(E), INDEX)
Arguments
ARRAY(E)
ARRAY(E)
Type: array
A array of values of any type.
INDEX
INDEX
Type: integer
An integer indicating the index of the element to retrieve.
If INDEX > 0
, this function provides the same functionality as the SQL-standard subscript operator ([]
).
If INDEX < 0
, this function accesses elements from the last to the first.
Note that if the ARRAY
contains any null
values, they are ignored in the indexing of the array.
Returns
Type: same as input
The element of ARRAY
at the given INDEX
.
Note that if the INDEX
is 0, then the function returns null
.
Examples
ARRAY
INDEX
Output
[5,2,3,1,2,3]
1
5
[5,2,3,1,2,3]
4
1
[5,2,3,1,2,3]
10
NULL
[5,2,3,1,6,3]
-2
6
[a,2,p,34]
3
p
Transformation job example
SQL
CREATE JOB function_operator_example
ADD_MISSING_COLUMNS = true
AS INSERT INTO default_glue_catalog.upsolver_samples.orders_transformed_data
MAP_COLUMNS_BY_NAME
SELECT
ELEMENT_AT(ARRAY['a','2','','34'], 3) AS element_example1,
ELEMENT_AT(ARRAY[1,2,3,null::bigint,4,5], 4) AS element_example2
FROM default_glue_catalog.upsolver_samples.orders_raw_data
WHERE $commit_time BETWEEN run_start_time() AND run_end_time()
GROUP BY shippinginfo_address_state
LIMIT 1;
Query result
element_example1
element_example2
''
4
Last updated