How to explode an object using Amazon Athena?-CodePudding

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)

CodePudding user response：

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