如何在BigQuery WHERE子句中访问event_params.key并获取数组值

一、WHERE子句中筛选特定key的参数 #

event_params是数组类型，没法直接用event_params.key["KEYNAME"]这种写法筛选，给你两种实用方法：

方法1：展开数组后筛选（适合要查看所有参数明细的场景） #

用UNNEST把数组拆成单独的行，再针对每个参数的key和value筛选：

SELECT 
  timestamp AS data,
  event_name AS event,
  param.key AS key,
  param.value AS value
FROM `instance_name.dt_name.db_name`,
UNNEST(event_params) AS param
WHERE
  param.key = "KEYNAME"
  AND param.value.string_value = "VALUE-STRING_VALUE"
ORDER BY timestamp ASC

方法2：用EXISTS过滤整行（适合只保留符合条件的完整事件行） #

如果不需要展开所有参数，只想保留包含指定key和对应值的事件行，用EXISTS子查询更高效：

SELECT 
  timestamp AS data,
  event_name AS event,
  event_params
FROM `instance_name.dt_name.db_name` 
WHERE EXISTS (
  SELECT 1 FROM UNNEST(event_params) AS param
  WHERE param.key = "KEYNAME"
    AND param.value.string_value = "VALUE-STRING_VALUE"
)
ORDER BY timestamp ASC

二、提取不同类型的value值 #

event_params.value是结构体，包含string_value、int_value、float_value等字段，直接根据参数类型提取就行：

提取字符串类型值 #

SELECT 
  param.key,
  param.value.string_value AS string_value
FROM `instance_name.dt_name.db_name`,
UNNEST(event_params) AS param
WHERE param.key = "KEYNAME"

提取整数/浮点数类型值 #

SELECT 
  param.key,
  param.value.int_value AS int_value,
  param.value.float_value AS float_value
FROM `instance_name.dt_name.db_name`,
UNNEST(event_params) AS param
WHERE param.key IN ("INT_PARAM_KEY", "FLOAT_PARAM_KEY")

统一提取值（兼容多种类型） #

如果不确定参数的类型，可以用COALESCE按优先级提取并统一转成字符串：

SELECT 
  param.key,
  COALESCE(
    CAST(param.value.string_value AS STRING),
    CAST(param.value.int_value AS STRING),
    CAST(param.value.float_value AS STRING)
  ) AS unified_value
FROM `instance_name.dt_name.db_name`,
UNNEST(event_params) AS param

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