0

I have a bunch of tables in Athena that contain structs with different nested columns. I'd like to query from all of those tables as a single table (ie, a union view) and I'd like to be able to return the nested column from the struct only if it exists, otherwise return a null.

Example:

Table1: 
email
record: {email, first_name, last_name}

Table2:
email
record: {email, dob}

I'd like to be able to union those and then query on the common email column. Then do something like record.first_name to generate a column populated with data from Table1 rows and a null value for Table 2 rows.

I've tried wrapping a try() block around the attribute, and that resulted in a "column cannot be resolved" error.

Is this possible? Or do I need to define the struct columns in the view/union query?

kid_drew
  • 3,857
  • 6
  • 28
  • 38
  • One solution may be to use TRANSFORM on each element you want to extract. https://stackoverflow.com/questions/48472110/unnesting-in-sql-athena-how-to-convert-array-of-structs-into-an-array-of-valu – June7 Jun 30 '20 at 20:21

1 Answers1

1

You can cast record as JSON, like this:

SELECT
  email,
  CAST(record AS JSON) AS record
FROM table1

UNION ALL

SELECT
  email,
  CAST(record AS JSON) AS record
FROM table2

I'm sure you can cast to MAP as well, but I couldn't figure out the syntax.

Theo
  • 131,503
  • 21
  • 160
  • 205