How to create a report in Biquery with param.key value “action” & “label” in two different...
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.
google-bigquery
asked Nov 22 '18 at 5:44
Ramakrishnan MRamakrishnan M
24
add a comment |
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.
google-bigquery
asked Nov 22 '18 at 5:44
Ramakrishnan MRamakrishnan M
24
add a comment |
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.
google-bigquery
asked Nov 22 '18 at 5:44
Ramakrishnan MRamakrishnan M
24
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.
google-bigquery
google-bigquery
asked Nov 22 '18 at 5:44
Ramakrishnan MRamakrishnan M
24
asked Nov 22 '18 at 5:44
Ramakrishnan MRamakrishnan M
24
asked Nov 22 '18 at 5:44
Ramakrishnan MRamakrishnan M
24
asked Nov 22 '18 at 5:44
Ramakrishnan MRamakrishnan M
24
asked Nov 22 '18 at 5:44
Ramakrishnan MRamakrishnan M
24
24
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
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
answered Nov 22 '18 at 6:00
Mikhail BerlyantMikhail Berlyant
56.9k43570
Mikhail, thanks for the response, but bigquery showing red error dot before line No. 4 param2.value.string_value as label, for the First option provided by you sir. Pls. help
– Ramakrishnan M
Nov 22 '18 at 6:21
I was writing on-go so it is possible - give me sec - i will check
– Mikhail Berlyant
Nov 22 '18 at 6:24
Thanks Mikhail for your quick response. Very kind of you Sir
– Ramakrishnan M
Nov 22 '18 at 6:26
it works for me - what error message it shows?
– Mikhail Berlyant
Nov 22 '18 at 6:32
I just checked second query and it also works for me - so give me the exact error you see!
– Mikhail Berlyant
Nov 22 '18 at 6:34
|
show 16 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53424565%2fhow-to-create-a-report-in-biquery-with-param-key-value-action-label-in-two%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
answered Nov 22 '18 at 6:00
Mikhail BerlyantMikhail Berlyant
56.9k43570
Mikhail, thanks for the response, but bigquery showing red error dot before line No. 4 param2.value.string_value as label, for the First option provided by you sir. Pls. help
– Ramakrishnan M
Nov 22 '18 at 6:21
I was writing on-go so it is possible - give me sec - i will check
– Mikhail Berlyant
Nov 22 '18 at 6:24
Thanks Mikhail for your quick response. Very kind of you Sir
– Ramakrishnan M
Nov 22 '18 at 6:26
it works for me - what error message it shows?
– Mikhail Berlyant
Nov 22 '18 at 6:32
I just checked second query and it also works for me - so give me the exact error you see!
– Mikhail Berlyant
Nov 22 '18 at 6:34
|
show 16 more comments
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
answered Nov 22 '18 at 6:00
Mikhail BerlyantMikhail Berlyant
56.9k43570
Mikhail, thanks for the response, but bigquery showing red error dot before line No. 4 param2.value.string_value as label, for the First option provided by you sir. Pls. help
– Ramakrishnan M
Nov 22 '18 at 6:21
I was writing on-go so it is possible - give me sec - i will check
– Mikhail Berlyant
Nov 22 '18 at 6:24
Thanks Mikhail for your quick response. Very kind of you Sir
– Ramakrishnan M
Nov 22 '18 at 6:26
it works for me - what error message it shows?
– Mikhail Berlyant
Nov 22 '18 at 6:32
I just checked second query and it also works for me - so give me the exact error you see!
– Mikhail Berlyant
Nov 22 '18 at 6:34
|
show 16 more comments
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
answered Nov 22 '18 at 6:00
Mikhail BerlyantMikhail Berlyant
56.9k43570
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
answered Nov 22 '18 at 6:00
Mikhail BerlyantMikhail Berlyant
56.9k43570
edited Nov 22 '18 at 6:10
answered Nov 22 '18 at 6:00
Mikhail BerlyantMikhail Berlyant
56.9k43570
answered Nov 22 '18 at 6:00
Mikhail BerlyantMikhail Berlyant
56.9k43570
answered Nov 22 '18 at 6:00
Mikhail BerlyantMikhail Berlyant
56.9k43570
56.9k43570
Mikhail, thanks for the response, but bigquery showing red error dot before line No. 4 param2.value.string_value as label, for the First option provided by you sir. Pls. help
– Ramakrishnan M
Nov 22 '18 at 6:21
I was writing on-go so it is possible - give me sec - i will check
– Mikhail Berlyant
Nov 22 '18 at 6:24
Thanks Mikhail for your quick response. Very kind of you Sir
– Ramakrishnan M
Nov 22 '18 at 6:26
it works for me - what error message it shows?
– Mikhail Berlyant
Nov 22 '18 at 6:32
I just checked second query and it also works for me - so give me the exact error you see!
– Mikhail Berlyant
Nov 22 '18 at 6:34
|
show 16 more comments
Mikhail, thanks for the response, but bigquery showing red error dot before line No. 4 param2.value.string_value as label, for the First option provided by you sir. Pls. help
– Ramakrishnan M
Nov 22 '18 at 6:21
I was writing on-go so it is possible - give me sec - i will check
– Mikhail Berlyant
Nov 22 '18 at 6:24
Thanks Mikhail for your quick response. Very kind of you Sir
– Ramakrishnan M
Nov 22 '18 at 6:26
it works for me - what error message it shows?
– Mikhail Berlyant
Nov 22 '18 at 6:32
I just checked second query and it also works for me - so give me the exact error you see!
– Mikhail Berlyant
Nov 22 '18 at 6:34
Mikhail, thanks for the response, but bigquery showing red error dot before line No. 4 param2.value.string_value as label, for the First option provided by you sir. Pls. help
– Ramakrishnan M
Nov 22 '18 at 6:21
Mikhail, thanks for the response, but bigquery showing red error dot before line No. 4 param2.value.string_value as label, for the First option provided by you sir. Pls. help
– Ramakrishnan M
Nov 22 '18 at 6:21
I was writing on-go so it is possible - give me sec - i will check
– Mikhail Berlyant
Nov 22 '18 at 6:24
I was writing on-go so it is possible - give me sec - i will check
– Mikhail Berlyant
Nov 22 '18 at 6:24
Thanks Mikhail for your quick response. Very kind of you Sir
– Ramakrishnan M
Nov 22 '18 at 6:26
Thanks Mikhail for your quick response. Very kind of you Sir
– Ramakrishnan M
Nov 22 '18 at 6:26
it works for me - what error message it shows?
– Mikhail Berlyant
Nov 22 '18 at 6:32
it works for me - what error message it shows?
– Mikhail Berlyant
Nov 22 '18 at 6:32
I just checked second query and it also works for me - so give me the exact error you see!
– Mikhail Berlyant
Nov 22 '18 at 6:34
I just checked second query and it also works for me - so give me the exact error you see!
– Mikhail Berlyant
Nov 22 '18 at 6:34
|
show 16 more comments
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53424565%2fhow-to-create-a-report-in-biquery-with-param-key-value-action-label-in-two%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
