How to Unpivot a Struct in BigQuery?

Question

I have a table with an ID and then a struct. I want to turn each element of the struct into a new row with the struct field name being a value in the column Period and the value being the structures value. See table below

Query that generated the table:

Current data

I tried this:

    SELECT * FROM `business-analytics-workbench.RAW.User_Activity` as UA
    UNPIVOT(Activity FOR PERIOD in (Last_7_Days,Last_14_Days,Last_30_Days,Last_90_Days,First_Date_AEST,Last_Date_AEST))

But I get this error "Unrecognized name: Last_7_Days at [3:33]"

Answer 1

You cannot unpivot columns with different data types (in your example those are INT64 and DATE). So consider below approach

SELECT * FROM (
  SELECT Document_ID, User_Activity.* 
  FROM `business-analytics-workbench.RAW.User_Activity` as UA
)
UNPIVOT(Activity FOR PERIOD in (Last_7_Days,Last_14_Days,Last_30_Days,Last_90_Days))    

If applied to sample data in y our question output is