Is there a way to convert the following data (an array of "tuples") in Athena SQL to the following format?
Note: that the number of items in the array is not defined and there can be many, but they are always paired.
From:
[1234, 1, 5678, 2, ..., 9012, 3]
To:
| ID | Val |
|---|---|
| 1234 | 1 |
| 5678 | 2 |
| 9012 | 3 |
With provided data you can try next approach - unnest the array with ordinality, group using the ordinality into array and then extract members of the array:
select try(agg [ 1 ]) id,
try(agg [ 2 ]) val
from (
select (a - 1) / 2 grp, -- group determined from ordinality
array_agg(n) agg -- aggregated array with up to 2 members
from (select array[1234, 1, 5678, 2, 9012, 3] arr) -- initial data
cross join unnest(arr) WITH ORDINALITY AS t (n, a)
group by 1
)
order by grp
Output:
| id | val |
|---|---|
| 1234 | 1 |
| 5678 | 2 |
| 9012 | 3 |