Using UNNEST for a column with (n) values duplicates all values for (n) rows-CodePudding

I have a single record with column (type) containing an array of values, e.g. [Map,Table,Pie]

Using Athena I need to to flatten this record into 3 separate records each with one value from the array in the (type) column.

SELECT type
FROM
athenadb.table,
UNNEST(type) as charttyp

This is the result of this query, three identical records.

1 [Map, Pie, Table]
2 [Map, Pie, Table] 
3 [Map, Pie, Table]

What am I missing here ? Clearly on one hand it recognizes the array length = 3 but does not parse the array elements...

CodePudding user response：

SELECT ntype
FROM
athenadb.table,
UNNEST(type) as t(ntype)

CodePudding user response：

You are missing specifying the alias for column and using it in the resulting select as described in the documentation (trino one):

SELECT flattened_type
FROM athenadb.table,
UNNEST(type) as t(flattened_type)