Is there a way to flatten a struct field in AWS Athena?

user_8569490 0
Viewed 1053

I'm trying to write an Athena query which will allow me to expand a struct inside of a select statement.

Lets say I have a table my_table which looks like:

id string,
metadata struct<field1:varchar,field2:int>

I know that by placing the name field1 in the query I can pull the field from the struct, but I would like something more general such as metadata.* so schema can be abstract.

This can easily be done with Python and some libraries but wanted to check if there isn't anything that can be done inside Athena.

Thanks!

presto amazon-athena hive amazon-s3 json
1 Answers

I would like it if something like select results.id, results.scores.* from my_table results worked, but unfortunately it doesn't.

What you're asking for is called "generalized all fields reference". It is available in Presto since Presto 323. You can download the latest Presto release from https://trino.io/download.html.

Unfortunately, Athena is currently based on Presto .172 (released 3 years ago), so this functionality is not available there. Until Athena is upgraded, there is no workaround you can apply on SQL level. You need to compensate this within application reading the data from Athena. Or, you can deploy recent Presto version on AWS easily.

user_654580
Related