Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

struct - extract array of arrays in presto

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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)
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

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...