BigQuery: How to access all the distinct event_params.key of a event_name

Question

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"

Answer 1

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"