A single row in Athena table is stored as —
select id, course, date from demo.course_tab where id='1234567892' id course date 1234567892 [95c3c1bc5873, 2e345b2eb678, 027b02599f4a, 8695a580520b, 5d453355d415, cdcc7682070b] 2019-06-13
The datatype for course column is array(string). Now, how can you get the output in below format –
id course date 1 1234567892 95c3c1bc5873 2019-06-13 2 1234567892 2e345b2eb678 2019-06-13 3 1234567892 027b02599f4a 2019-06-13 4 1234567892 8695a580520b 2019-06-13 5 1234567892 5d453355d415 2019-06-13 6 1234567892 cdcc7682070b 2019-06-13
To get the desired output in Athena, simply use CROSS JOIN in conjunction with UNNEST operator —
SELECT id, course_id , date FROM demo.course_tab cross join unnest(course) as t(course_id) where id = '103314692'
Reference –
https://docs.aws.amazon.com/athena/latest/ug/flattening-arrays.html
One thought on “Expanding array to multiple rows – Athena”
Really useful!! Good one!