SPLIT Bigquery Row Content into New Separate Columns

Question

I have the following requirement as per the screenshot attached.

Need the content of a nested columns into new separate columns as per the screenshot attached. I only need 3 values from the Column event_params.key 1. percentage 2.seconds 3. activity_id and its value will take from column event_params.value.double_value

Any ideas of how to achieve that?

Answer 1

To extract a row from an array of struct (nested struct) by specific key/value in Google BigQuery we need to unnest array first and then extract key-value pairs manually using subquery:

with test_table as (
  select 
    '20201014' as event_date,
    '112321341234' as event_timestamp,
    'spent_time' as event_name, 
    [ (select as struct 'percentage' as key, 0.0 as double_value), 
      (select as struct 'seconds' as key, 0.0 as double_value),
      (select as struct 'activity_id' as key, 88.0 as double_value)
    ] as event_params
  union all
  select 
    '20201014999' as event_date,
    '112321341234999' as event_timestamp,
    'spent_time999' as event_name, 
    [ (select as struct 'percentage' as key, 9.0 as double_value), 
      (select as struct 'seconds' as key, 0.9 as double_value),
      (select as struct 'activity_id' as key, 99.0 as double_value)
    ] as event_params
)
select 
  event_date,
  event_timestamp,
  event_name,
  (select double_value from unnest(event_params) where key = 'percentage') as percentage,
  (select double_value from unnest(event_params) where key = 'seconds') as seconds,
  (select double_value from unnest(event_params) where key = 'activity_id') as activity_id
from test_table