Qfyilyi

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.

try below

#standardSQL

select 

  event_name, 

  param1.value.string_value as action,

  param2.value.string_value as label,

  count(1) as eventcount

From `<table>`,

UNNEST(event_params) as param1,

UNNEST(event_params) as param2

where event_name = 'BotNav' 

and param1.key='action' 

and param2.key='label' 

group by 1, 2, 3 

order by eventcount desc   

obviously assuming the only one 'action' and 'label' keys in event_params per event_name row - which should be true based on count numbers from examples in question

Another option would be

#standardSQL

select 

  event_name, 

  (select value.string_value from UNNEST(event_params) where key='action') as action,

  (select value.string_value from UNNEST(event_params) where key='label') as label,

  count(1) as eventcount

From `<table>`

where event_name = 'BotNav' 

group by 1, 2, 3 

order by eventcount desc 

with same assumption