Search code examples
sqllistarraylistamazon-athenapresto

Athena query get the index of any element in a list

I need to access to the elements in a column whose type is list according to the other elements' locations in another list-like column. Say, my dataset is like:

WITH dataset AS (
  SELECT ARRAY ['hello', 'amazon', 'athena'] AS words,
  ARRAY ['john', 'tom', 'dave'] AS names
)
SELECT * FROM dataset

And I'm going to achieve

SELECT element_at(words, index(names, 'john')) AS john_word
FROM dataset

Is there a way to have a function in Athena like "index"? Or how can I customize one like this? The desired result should be like:

| -------- |
| john_word|
| -------- |
| hello    |
| -------- |
Solution

  • array_position:

    array_position(x, element) → bigint
    Returns the position of the first occurrence of the element in array x (or 0 if not found).

    Note that in presto array indexes start from 1.

    SELECT element_at(words, array_position(names, 'john')) AS john_word
FROM dataset