ga_code_0005_UNNEST.sql

SELECT 
 
  event_date, event_timestamp, event_name, 

  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "area") AS area, 

  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "businessName") AS business_name, 

  (SELECT value.int_value FROM UNNEST(event_params) WHERE key = "gallerySize") AS gallery_size, 

 
  CONCAT(
    CAST((SELECT value.double_value FROM UNNEST(event_params) WHERE key = "latitude") AS STRING)
    , ', ',
    CAST((SELECT value.double_value FROM UNNEST(event_params) WHERE key = "longitude") AS STRING)
  ) 
  AS lat_long, 

  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "type") AS type, 

  CASE WHEN REGEXP_EXTRACT((SELECT value.string_value FROM UNNEST(event_params) WHERE key = "id"), r'[a-z]+') = "poi" 
       THEN REGEXP_EXTRACT( (SELECT value.string_value FROM UNNEST(event_params) WHERE key = "id"), r'[0-9]+')  
  ELSE null 
   END as poi_id, 
FROM
  example_project.analytics_348840650.events_*` 
WHERE 
    REGEXP_EXTRACT(_table_suffix, r'[0-9]+')
    BETWEEN FORMAT_DATE('%Y%m%d',DATE_SUB(CURRENT_DATE(), INTERVAL 80 DAY)) 
       AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())