How to flatten a struct in bigquery standard sql?

Question

With the below standard sql query, I can return a table of structs in BigQuery that contains all fields from both a and b.

SELECT a, b
FROM first_table a
JOIN second_table b
ON a.key = b.key;

The resulting table schema will have a as RECORD, and b as RECORD, with a.field1, a.field2, b.field1, b.field2, etc. all having their original types.

If I save this table as a_join_b and then query it in legacy_sql:

SELECT *
FROM a_join_b
;

it will flatten the structs and provide a table with fields named a_field1, a_field2, ..., b_field1, b_field2. Note the underscores between the table names and the field names, and that a and b can have similar field names.

Is there a way in BigQuery Standard SQL to flatten a table without referring to individual record names? I've looked at UNNEST in How to convert a nested flatten into Standard SQL but the solution seems to require joining unnested fields that must be named.

Maximilian Maximilian · Accepted Answer · 2017-08-24T22:55:21

If a and b don't have arrays, it's very simple:

SELECT *
FROM first_table a
JOIN second_table b
USING (key)

or

SELECT a.*, b.* EXCEPT (key)
FROM first_table a
JOIN second_table b
ON a.key = b.key

If they do contain arrays, then it's dependent on the layout of those (UNNEST refers to arrays only, so I suspect they may). Can you clarify the layout of the tables if so?

How to flatten a struct in bigquery standard sql?

1 Answers