Search code examples
sqlamazon-athenaprestotrino

How to explode an object using Amazon Athena?

I have a table, myTable, with a column, meta_data, where each row has an object with this structure: {prop_1: 'some_value', prop_2: 17 }.

How do I transform this table so that it will look like:

prop_1 prop_2
'some_value' 17
... ...

I tried this:

SELECT meta_data.*
FROM myTable
CROSS JOIN UNNEST(meta_data) AS meta_data

But it resulted in INVALID_FUNCTION_ARGUMENT: Cannot unnest type: row(prop_1 varchar, prop_2 integer)

Solution

  • Just select appropriate fields from row:

    SELECT meta_data.prop_1, meta_data.prop_2
FROM myTable

    or try creating table alias and expanding via it (works in latest Trino version at least):

    SELECT t.meta_data.*
FROM myTable t