google bigquery - How to create a report in Biquery with param.key value “action” & “label” in two different columns

Question

Queried param.key “action” with string values via through big query by using below query and fetch the report

select event_name, param.value.string_value as action,count(*) as eventcoun From <table>, UNNEST (event_params) as param where event_name = 'BotNav' and param.key='action' group by 1,2 order by eventcount desc

Output received through bigquery

event_name action eventount

BotNav Home 3575

BotNav App 1468

Queried param.key “label” with string values via through big query by using below query and fetch the report

select event_name, param.value.string_value as action,count(*) as eventcoun From <table>, UNNEST (event_params) as param where event_name = 'BotNav' and param.key='label' group by 1,2 order by eventcount desc

output received through bigquery event_name label eventount

BotNav click 2341

BotNav submit 1234

BotNav click 1234 BotNav submit 234

How to create a report with event.param .key “action” & “label” in two different columns. I need to out in Bigquery like this. Pls. find below the expected output in bigquery

Expected Output

event_name action label eventount

BotNav Home click 2341

BotNav Home submit 1234

BotNav App click 1234

BotNav App submit 234

Pls Help to get the action & label string value in two different columns.

Answer 1

Waitting for answers