AWSAWS Athena

Expanding array to multiple rows – Athena

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

Leave a Reply