Athena UNION – do I need to define struct columns?

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?

Answer

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.