How to Unpivot a Struct in BigQuery?

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:

Click to see query that generated the table

Current data

Click to see table structure

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]"

3 answers

  • answered 2021-09-12 21:55 ggordon

    Since these are part of a struct, try using the full struct field name

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

    Let me know if this works for you.

  • answered 2021-09-12 22:30 Mikhail Berlyant

    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

    enter image description here

  • answered 2021-09-12 22:43 Mikhail Berlyant

    If you are willing to pivot table without using PIVOT operator - in this case you have less restriction - consider below example

    select Document_ID, split(kv, ':')[offset(0)] PERIOD, split(kv, ':')[offset(1)] Activit
    from (
      select Document_ID, User_Activity.* 
      from `business-analytics-workbench.RAW.User_Activity`
    ) t,
    unnest(split(translate(to_json_string(t), '{}"', ''))) kv
    where split(kv, ':')[offset(0)] != 'Document_ID'         
    

    if to apply to sample data in your question - output is

    enter image description here

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum