sql - BigQuery: distinct values in key column

Question

I have a table with event_params.key column

I want to see all possible values in this column:

When I try to select distinct values:

SELECT  distinct event_params.key FROM `data.analytics.events_*`

I get an error...

So I know how to extract values, but for the next step, I need to know all possible distinct values and their types in the key column ( int, float, string..)

How to do it?

question from:https://stackoverflow.com/questions/65871016/bigquery-distinct-values-in-key-column

Answer 1

Use unnest():

SELECT ep.key, COUNT(*)
FROM `data.analytics.events_*` e CROSS JOIN
     unnest(event_params) ep
GROUP BY ep.key;

Note: This can be quite expensive if your data is large.