如何编写SQL查询解析BigQuery的ga_sessions表嵌套JSON商品字段

GA Sessions表中的hits、hits.product均为数组类型，硬编码OFFSET(0)只能取到数组的第一条元素，要获取全部嵌套的商品数据，使用UNNEST函数拆解数组即可：

SELECT 
  visitId,
  p.productSKU,
  p.v2ProductName
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`,
UNNEST(hits) AS h,
UNNEST(h.product) AS p
LIMIT 5

说明 #

• 语句中的逗号等效于CROSS JOIN，会将数组拆解后的每行数据和原表行做关联，输出所有存在商品关联的会话记录
• 如果需要保留没有命中记录、或者命中记录没有关联商品的会话，可以把逗号替换为LEFT JOIN UNNEST(...) ON TRUE

内容的提问来源于stack exchange，提问作者shazufaraz