Convert an array of tuples in Athena SQL to a formatted table-CodePudding

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

CodePudding user response：

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