Get value from array based on index

Question

Quite new with Athena and have data structured as below:

index	array
[1]	[1,2,3]
[2]	[2,3,4]

And if I want to get the specific value from each array based on the value from the index column, how to get the below output? e.g the expected output should be 1 and 3 based on the index provided

Answer 1

Athena docs for accessing an array element: https://docs.aws.amazon.com/athena/latest/ug/accessing-array-elements.html

• If index is an integer:

SELECT element_at(array, index)
FROM your_table

• If index is an array:

SELECT element_at(array, index[1])
FROM your_table