BigQuery Standard SQL如何排除hits双层嵌套数组的指定列

实现方案 #

Standard SQL 不支持直接用 EXCEPT 排除嵌套在子数组内的字段，你可以通过拆分+重组customDimensions子数组的方式实现需求，修改后的完整查询语句如下：

SELECT 
  fullVisitorId,
  visitId,
  visitNumber,
  cd.value as PCF_CUST_ID,
  date,
  TIMESTAMP_SECONDS(visitStartTime) as visitStartTime,
  totals.visits as visits,
  totals.hits as total_hits,
  -- 提取hits除customDimensions外的所有原生字段
  hits.* EXCEPT (customDimensions),
  -- 处理customDimensions数组，排除指定字段后重组回原结构
  ARRAY(
    SELECT customDimension.* EXCEPT (value, index)
    FROM UNNEST(hits.customDimensions) AS customDimension
  ) AS customDimensions
FROM `lt-pcf-analytics-exp.90676036.ga_sessions_*` as t
LEFT JOIN UNNEST(customDimensions) as cd
LEFT JOIN UNNEST(hits) as hits
WHERE 
  _TABLE_SUFFIX BETWEEN '20210101' AND '20210131' 
  AND cd.index = 4 
  AND cd.value IS NOT NULL
ORDER BY PCF_CUST_ID, visitStartTime, hitNumber

逻辑说明

• 该写法完全保留hits原有字段结构，仅排除指定的两个嵌套字段，不需要调整后续的字段引用逻辑
• 如果你不需要保留customDimensions数组结构，也可以直接删除customDimensions相关的处理语句，直接取hits下其他字段即可

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