UNNEST event_params后报错ep表缺失数据集该如何正确引用

错误原因 #

• 你的SQL中(SELECT ep.value FROM ep WHERE ep.key = 'key_name')子查询里，ep是外层查询UNNEST(event_params)得到的临时行别名，不是物理表/视图对象，数据库解析时会将FROM ep识别为查询物理表ep，找不到对应数据集就抛出你遇到的错误。

正确写法 #

你需要获取的是同一事件行中key为key_name的参数值，直接从当前行的event_params数组中查询即可，参考写法：

SELECT 
  (SELECT value FROM UNNEST(e.event_params) WHERE key = 'key_name') AS key_name
FROM analytics_tables.events_20210824 e 
WHERE e.event_name = 'item_clicked' 
AND EXISTS (
  SELECT 1 FROM UNNEST(e.event_params) AS ep
  WHERE ep.key = 'my_key_type' AND ep.value.int_value = 7
);

如果你的业务逻辑是保留所有my_key_type=7的参数行，同时关联同事件的key_name值，也可以用下面的写法：

SELECT 
  (SELECT value FROM UNNEST(e.event_params) WHERE key = 'key_name') AS key_name,
  ep.*
FROM analytics_tables.events_20210824 e 
CROSS JOIN UNNEST(e.event_params) AS ep
WHERE e.event_name = 'item_clicked' 
AND ep.key = 'my_key_type' AND ep.value.int_value = 7;

核心注意点 #

• 不要将UNNEST输出的行别名作为FROM后的查询数据源，要取同事件的其他参数，直接对当前行的event_params数组做UNNEST查询即可。

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