Flattening a Repeated Field

February 02, 2015
By Bounteous

Legacy SQL

SELECT 
     CONCAT(fullVisitorId, ".", STRING(visitId)) AS Session_ID,
     hits.page.pagePath AS Page
FROM
     FLATTEN([google.com:analytics-bigquery:LondonCycleHelmet.ga_sessions_20130910], hits)

Standard SQL

SELECT 
     CONCAT(fullVisitorId, ".", CAST(visitId AS STRING)) AS Session_ID,
     hits.page.pagePath AS Page
FROM   
  'google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910' AS GA,
  UNNEST(GA.hits) AS hits