google-bigquery - UNNEST event_params 后表名缺失数据集

Question

我正在尝试执行以下查询：

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

但我收到此错误消息：

表名“ep”缺少数据集，而请求中未设置默认数据集

我应该如何引用“ep”才能使其正常工作？

标签： google-bigquery

Answer 1

试试下面

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