如何在BigQuery中将嵌套行转为无空值的独立列？

执行以下查询获取包含嵌套Record类型user_properties的结果：

SELECT
  event_name,
  user_pseudo_id,
  event_timestamp,
  user_properties
FROM
  `project.analytics_000.events_*`

尝试用UNNEST展开数据时，得到大量含空值的拆分行，且使用GROUP BY报错：

SELECT
  event_name,
  event_timestamp,
  user_pseudo_id,
  CASE WHEN user_data.key = 'customer_type' THEN user_data.value.string_value ELSE NULL END AS customer_type,
  CASE WHEN user_data.key = 'has_sales_tag' THEN user_data.value.string_value ELSE NULL END AS has_sales_tag,
FROM
  `blueray-cargo.analytics_296834888.events_*`,
UNNEST(user_properties) AS user_data

需要将嵌套行转为无空值的独立列。

方法一：聚合函数+GROUP BY #

利用MAX（或ANY_VALUE）聚合函数忽略空值，结合GROUP BY将同一事件的拆分结果合并为单行：

SELECT
  event_name,
  event_timestamp,
  user_pseudo_id,
  MAX(CASE WHEN user_data.key = 'customer_type' THEN user_data.value.string_value END) AS customer_type,
  MAX(CASE WHEN user_data.key = 'has_sales_tag' THEN user_data.value.string_value END) AS has_sales_tag
FROM
  `blueray-cargo.analytics_296834888.events_*`,
UNNEST(user_properties) AS user_data
GROUP BY
  event_name, event_timestamp, user_pseudo_id

原理：UNNEST会把每个user_properties的key拆成单独一行，同一事件会生成多行（每个key对应一行）。MAX函数自动忽略NULL值，GROUP BY通过事件唯一标识（event_name、event_timestamp、user_pseudo_id）将多行合并为一行，每个列仅保留对应key的非空值。

方法二：PIVOT语法（更简洁） #

用PIVOT直接将key转换为列，适合提取多个固定key的场景：

SELECT
  event_name,
  event_timestamp,
  user_pseudo_id,
  customer_type,
  has_sales_tag
FROM (
  SELECT
    event_name,
    event_timestamp,
    user_pseudo_id,
    user_data.key,
    user_data.value.string_value
  FROM
    `blueray-cargo.analytics_296834888.events_*`,
  UNNEST(user_properties) AS user_data
  WHERE user_data.key IN ('customer_type', 'has_sales_tag') -- 过滤无关key，提升效率
)
PIVOT(
  MAX(string_value)
  FOR key IN ('customer_type' AS customer_type, 'has_sales_tag' AS has_sales_tag)
)

原理：子查询先提取目标key和对应值，PIVOT将key字段的不同取值转为独立列，MAX确保每个列仅保留非空的对应值，最终得到无空值的结构化结果。

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