struct - extract array of arrays in presto

Question

I have a table in Athena (presto) with just one column named individuals and this is the type of then column:

array(row(individual_id varchar, ids array(row(type varchar, value varchar, score integer))))

I want to extract value from inside the ids and return them as a new array. As an example:

[{individual_id=B1Q, ids=[{type=H, value=efd3, score=1}, {type=K, value=NpS, score=1}]}, {individual_id=D6n, ids=[{type=A, value=178, score=6}, {type=K, value=NuHV, score=8}]}]

and I want to return

ids
[efd3, NpS, 178, NuHV]

I tried multiple solutions like

select * from "test"
CROSS JOIN UNNEST(individuals.ids.value) AS t(i)

but always return

 Expression individuals is not of type ROW

question from:https://stackoverflow.com/questions/65930870/extract-array-of-arrays-in-presto

Answer 1

select
  array_agg(ids.value)
from test
cross join unnest(test.individuals) t(ind)
cross join unnest(ind.ids) t(ids)

result:

[efd3, NpS, 178, NuHV]

that will return all the id values as one row, which may or may not be what you want

if you want to return an array of individual values by individual_id:

select
  ind.individual_id,
  array_agg(ids.value)
from test
cross join unnest(test.individuals) t(ind)
cross join unnest(ind.ids) t(ids)
group by
  ind.individual_id