英文:
BigQuery: How to access all the distinct event_params.key of a event_name
问题
我想了解特定 event_name 的可用 event_params.key。
我明白，要访问参数值，需要单独访问和展开数组的每个元素。例如，要访问每个事件的用户会话 ID 和按钮名称：
select user_id, event_name,
(select value.int_value from unnest(event_params) where key='ga_session_id') as session_id,
(select value.string_value from unnest(event_params) where key='button_name') as button_name
from `analytics_xx.events_*`
where user_id = 'abc'
但这假定我知道键是什么。如果我不知道呢？如何返回该 event_name 的所有可用键呢？
以下方法不起作用：
select event_name,
(select * from unnest(event_params)) as available_keys
from `analytics_xx.events_*`
where event_name = "button_click"
英文:
I am interesting in knowing the distinct event_params.key available for a particular event_name.
I understand that in order to access parameter values, each individual element of the array will need to be accessed and expanded individually. For instance, to access a user’s session_id and button_name per event:
select user_id, event_name,
(select value.int_value from unnest(event_params) where key='ga_session_id') as session_id,
(select value.string_value from unnest(event_params) where key='button_name') as button_name
from `analytics_xx.events_*`
where user_id = 'abc'
But this assumes I know the key. What if I don't? How can I return all the available keys for that event_name?
The following didn't work:
select event_name,
(select * from unnest(event_params)) as available_keys
from `analytics_xx.events_*`
where event_name = "button_click"
答案1
得分: 1
select distinct event_params.key
from analytics_xxx.events_*, unnest(event_params) as event_params
where event_name = "button_click"
英文:
One approach would be to unnest the event parameters and access the "key" attribute (distinct).
select distinct event_params.key
from `analytics_xxx.events_*`, unnest(event_params) as event_params
where event_name = "button_click"
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论