Renaming nested fields in BigQuery query

Question

Nested fields in BigQuery are selected using the dot operator.

I have change-data-captured tables with schemas that have a lot of nested fields. I would like to do something like this:

SELECT
   after.name as name, 
   after.age as age,
   after.address as address
FROM [mytable]

without having to manually specify the <root.leaf> as <leaf> clauses. I'm looking for something like:

SELECT after.* as <column suffix> from `mytable`

Is this possible?

EDIT: Additional info below

Note that my goal here is to remove the after prefix in the field names of the output (i.e. after.name or after_name becomes name).

Can you clarify what the expected versus actual output is? It's not clear why after.* does not give the expected results. — Elliott Brossard
Sorry it wasn't clear. Added the info in the description.after.* does give me the expected result. but the output column for after.name becomes after_name and so on. I want the output columns to just be name, age, address, etc. without the after_ prefix. — Charles V.

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2018-03-09T21:59:20

The only explanation I see for your problem to exist - is if you are still using BigQuery Legacy SQL. So, yes, in this case you will get below if use just SELECT field_name.*

#legacySQL
SELECT user_dim.device_info.*
FROM [firebase-analytics-sample-data:android_dataset.app_events_20160607]
LIMIT 2

result will be (i am stripping out most columns and show first two only here)

Row user_dim_device_info_device_category    user_dim_device_info_mobile_brand_name 
1   mobile                                  BLU      
2   mobile                                  Samsung

Whereas in BigQuery Standard SQL you will see below for same query

#standardSQL
SELECT user_dim.device_info.*
FROM `firebase-analytics-sample-data.android_dataset.app_events_20160607`
LIMIT 2   

Row device_category mobile_brand_name
1   mobile          BLU 
2   mobile          Samsung

So, to address your issue - just "simply" migrate to BigQuery Standard SQL

Renaming nested fields in BigQuery query

1 Answers