Bigquery - Transpose Array into columns
1
The following query returns a list of key/value pairs:
SELECT ["name:apple", "color:red"] as fruit;
result:
Is there a way to transpose the data so the results would be:
Update
I'm looking for a generalized solution, where the key and value and the array length of the results are unknown i.e: SELECT ["key0:val0", "key1:val1"...] as data;
arrays google-bigquery transpose
asked Nov 22 '18 at 9:09
Shlomi SchwartzShlomi Schwartz
3,1762073116
add a comment |
1
The following query returns a list of key/value pairs:
SELECT ["name:apple", "color:red"] as fruit;
result:
Is there a way to transpose the data so the results would be:
Update
I'm looking for a generalized solution, where the key and value and the array length of the results are unknown i.e: SELECT ["key0:val0", "key1:val1"...] as data;
arrays google-bigquery transpose
asked Nov 22 '18 at 9:09
Shlomi SchwartzShlomi Schwartz
3,1762073116
You'd need to write a query which writes a query... See @MikhailBerlyant's answer here stackoverflow.com/questions/39394111/…
– Bobbylank
Nov 22 '18 at 10:40
Please consider to accept the answer that you find most useful.
– MonicaPC
Nov 30 '18 at 0:30
add a comment |
1
1
1
2
The following query returns a list of key/value pairs:
SELECT ["name:apple", "color:red"] as fruit;
result:
Is there a way to transpose the data so the results would be:
Update
I'm looking for a generalized solution, where the key and value and the array length of the results are unknown i.e: SELECT ["key0:val0", "key1:val1"...] as data;
arrays google-bigquery transpose
asked Nov 22 '18 at 9:09
Shlomi SchwartzShlomi Schwartz
3,1762073116
The following query returns a list of key/value pairs:
SELECT ["name:apple", "color:red"] as fruit;
result:
Is there a way to transpose the data so the results would be:
Update
I'm looking for a generalized solution, where the key and value and the array length of the results are unknown i.e: SELECT ["key0:val0", "key1:val1"...] as data;
arrays google-bigquery transpose
arrays google-bigquery transpose
asked Nov 22 '18 at 9:09
Shlomi SchwartzShlomi Schwartz
3,1762073116
asked Nov 22 '18 at 9:09
Shlomi SchwartzShlomi Schwartz
3,1762073116
edited Nov 22 '18 at 10:13
Shlomi Schwartz
asked Nov 22 '18 at 9:09
Shlomi SchwartzShlomi Schwartz
3,1762073116
asked Nov 22 '18 at 9:09
Shlomi SchwartzShlomi Schwartz
3,1762073116
asked Nov 22 '18 at 9:09
Shlomi SchwartzShlomi Schwartz
3,1762073116
3,1762073116
You'd need to write a query which writes a query... See @MikhailBerlyant's answer here stackoverflow.com/questions/39394111/…
– Bobbylank
Nov 22 '18 at 10:40
Please consider to accept the answer that you find most useful.
– MonicaPC
Nov 30 '18 at 0:30
add a comment |
You'd need to write a query which writes a query... See @MikhailBerlyant's answer here stackoverflow.com/questions/39394111/…
– Bobbylank
Nov 22 '18 at 10:40
Please consider to accept the answer that you find most useful.
– MonicaPC
Nov 30 '18 at 0:30
You'd need to write a query which writes a query... See @MikhailBerlyant's answer here stackoverflow.com/questions/39394111/…
– Bobbylank
Nov 22 '18 at 10:40
You'd need to write a query which writes a query... See @MikhailBerlyant's answer here stackoverflow.com/questions/39394111/…
– Bobbylank
Nov 22 '18 at 10:40
Please consider to accept the answer that you find most useful.
– MonicaPC
Nov 30 '18 at 0:30
Please consider to accept the answer that you find most useful.
– MonicaPC
Nov 30 '18 at 0:30
add a comment |
3 Answers
3
active
oldest
votes
2
This should be a quick way to achieve your results:
#standardSQL
with items as (
select ["name:apple", "color:red"] p union all
select ["name:orange", "color:orange"] UNION ALL
select ["name:grapes", "color:green"]
),
arrayed as (
select
array_agg(
struct(
if(split(p, ":")[offset(0)] = 'name', split(p, ":")[offset(1)], '') as name,
if(split(p, ":")[offset(0)] = 'color', split(p, ":")[offset(1)], '') as color
)
) item from items, unnest(p) p
)
select
array((select i.name from unnest(item) i where i.name != '')) as name,
array((select i.color from unnest(item) i where i.color != '')) as color
from arrayed
answered Nov 22 '18 at 22:58
khankhan
1,93683052
add a comment |
1
I guess the BigQuery way would be using sub-selects on the array:
WITH t AS (SELECT * FROM UNNEST([
struct(['name:apple','color:red'] AS fruit),
struct(['name:pear','color:purple'] AS fruit)
]) )
SELECT
(SELECT SPLIT(f, ':')[SAFE_OFFSET(1)] FROM t.fruit f WHERE SPLIT(f, ':')[SAFE_OFFSET(0)]='name') AS name,
(SELECT SPLIT(f, ':')[SAFE_OFFSET(1)] FROM t.fruit f WHERE SPLIT(f, ':')[SAFE_OFFSET(0)]='color') AS color
FROM t
answered Nov 22 '18 at 10:02
Martin WeitzmannMartin Weitzmann
1,740512
Thanks for the response, please see my updates.
– Shlomi Schwartz
Nov 22 '18 at 10:14
1
That's not possible due to the nature of these data structures: an array has a dynamic quantity of elements, while structs or tables have elements with keys whose schema need to stay consistent over multiple rows
– Martin Weitzmann
Nov 22 '18 at 10:18
add a comment |
0
Not sure if there's a more succinct way to do this but this works
WITH CTE AS (
SELECT ["name:apple", "color:red"] as fruit
UNION ALL
SELECT ["name:pear", "color:green"]
),
CTE2 AS (
SELECT row_number() over () as rowNumber, fruit
FROM CTE
)
SELECT max(if(REGEXP_CONTAINS(fruit,'name:'),replace(fruit,'name:',''),null)) name,
max(if(REGEXP_CONTAINS(fruit,'color:'),replace(fruit,'color:',''),null)) color
FROM CTE2,
UNNEST(fruit) as fruit
GROUP BY rowNumber
answered Nov 22 '18 at 9:50
BobbylankBobbylank
1,038211
Thanks for the response, please see my updates.
– Shlomi Schwartz
Nov 22 '18 at 10:14
add a comment |
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
});
}
});
draft saved
draft discarded
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%2f53427308%2fbigquery-transpose-array-into-columns%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
This should be a quick way to achieve your results:
#standardSQL
with items as (
select ["name:apple", "color:red"] p union all
select ["name:orange", "color:orange"] UNION ALL
select ["name:grapes", "color:green"]
),
arrayed as (
select
array_agg(
struct(
if(split(p, ":")[offset(0)] = 'name', split(p, ":")[offset(1)], '') as name,
if(split(p, ":")[offset(0)] = 'color', split(p, ":")[offset(1)], '') as color
)
) item from items, unnest(p) p
)
select
array((select i.name from unnest(item) i where i.name != '')) as name,
array((select i.color from unnest(item) i where i.color != '')) as color
from arrayed
answered Nov 22 '18 at 22:58
khankhan
1,93683052
add a comment |
2
This should be a quick way to achieve your results:
#standardSQL
with items as (
select ["name:apple", "color:red"] p union all
select ["name:orange", "color:orange"] UNION ALL
select ["name:grapes", "color:green"]
),
arrayed as (
select
array_agg(
struct(
if(split(p, ":")[offset(0)] = 'name', split(p, ":")[offset(1)], '') as name,
if(split(p, ":")[offset(0)] = 'color', split(p, ":")[offset(1)], '') as color
)
) item from items, unnest(p) p
)
select
array((select i.name from unnest(item) i where i.name != '')) as name,
array((select i.color from unnest(item) i where i.color != '')) as color
from arrayed
answered Nov 22 '18 at 22:58
khankhan
1,93683052
add a comment |
2
2
2
This should be a quick way to achieve your results:
#standardSQL
with items as (
select ["name:apple", "color:red"] p union all
select ["name:orange", "color:orange"] UNION ALL
select ["name:grapes", "color:green"]
),
arrayed as (
select
array_agg(
struct(
if(split(p, ":")[offset(0)] = 'name', split(p, ":")[offset(1)], '') as name,
if(split(p, ":")[offset(0)] = 'color', split(p, ":")[offset(1)], '') as color
)
) item from items, unnest(p) p
)
select
array((select i.name from unnest(item) i where i.name != '')) as name,
array((select i.color from unnest(item) i where i.color != '')) as color
from arrayed
answered Nov 22 '18 at 22:58
khankhan
1,93683052
This should be a quick way to achieve your results:
#standardSQL
with items as (
select ["name:apple", "color:red"] p union all
select ["name:orange", "color:orange"] UNION ALL
select ["name:grapes", "color:green"]
),
arrayed as (
select
array_agg(
struct(
if(split(p, ":")[offset(0)] = 'name', split(p, ":")[offset(1)], '') as name,
if(split(p, ":")[offset(0)] = 'color', split(p, ":")[offset(1)], '') as color
)
) item from items, unnest(p) p
)
select
array((select i.name from unnest(item) i where i.name != '')) as name,
array((select i.color from unnest(item) i where i.color != '')) as color
from arrayed
answered Nov 22 '18 at 22:58
khankhan
1,93683052
answered Nov 22 '18 at 22:58
khankhan
1,93683052
answered Nov 22 '18 at 22:58
khankhan
1,93683052
answered Nov 22 '18 at 22:58
khankhan
1,93683052
1,93683052
add a comment |
add a comment |
1
I guess the BigQuery way would be using sub-selects on the array:
WITH t AS (SELECT * FROM UNNEST([
struct(['name:apple','color:red'] AS fruit),
struct(['name:pear','color:purple'] AS fruit)
]) )
SELECT
(SELECT SPLIT(f, ':')[SAFE_OFFSET(1)] FROM t.fruit f WHERE SPLIT(f, ':')[SAFE_OFFSET(0)]='name') AS name,
(SELECT SPLIT(f, ':')[SAFE_OFFSET(1)] FROM t.fruit f WHERE SPLIT(f, ':')[SAFE_OFFSET(0)]='color') AS color
FROM t
answered Nov 22 '18 at 10:02
Martin WeitzmannMartin Weitzmann
1,740512
Thanks for the response, please see my updates.
– Shlomi Schwartz
Nov 22 '18 at 10:14
1
That's not possible due to the nature of these data structures: an array has a dynamic quantity of elements, while structs or tables have elements with keys whose schema need to stay consistent over multiple rows
– Martin Weitzmann
Nov 22 '18 at 10:18
add a comment |
1
I guess the BigQuery way would be using sub-selects on the array:
WITH t AS (SELECT * FROM UNNEST([
struct(['name:apple','color:red'] AS fruit),
struct(['name:pear','color:purple'] AS fruit)
]) )
SELECT
(SELECT SPLIT(f, ':')[SAFE_OFFSET(1)] FROM t.fruit f WHERE SPLIT(f, ':')[SAFE_OFFSET(0)]='name') AS name,
(SELECT SPLIT(f, ':')[SAFE_OFFSET(1)] FROM t.fruit f WHERE SPLIT(f, ':')[SAFE_OFFSET(0)]='color') AS color
FROM t
answered Nov 22 '18 at 10:02
Martin WeitzmannMartin Weitzmann
1,740512
Thanks for the response, please see my updates.
– Shlomi Schwartz
Nov 22 '18 at 10:14
1
That's not possible due to the nature of these data structures: an array has a dynamic quantity of elements, while structs or tables have elements with keys whose schema need to stay consistent over multiple rows
– Martin Weitzmann
Nov 22 '18 at 10:18
add a comment |
1
1
1
I guess the BigQuery way would be using sub-selects on the array:
WITH t AS (SELECT * FROM UNNEST([
struct(['name:apple','color:red'] AS fruit),
struct(['name:pear','color:purple'] AS fruit)
]) )
SELECT
(SELECT SPLIT(f, ':')[SAFE_OFFSET(1)] FROM t.fruit f WHERE SPLIT(f, ':')[SAFE_OFFSET(0)]='name') AS name,
(SELECT SPLIT(f, ':')[SAFE_OFFSET(1)] FROM t.fruit f WHERE SPLIT(f, ':')[SAFE_OFFSET(0)]='color') AS color
FROM t
answered Nov 22 '18 at 10:02
Martin WeitzmannMartin Weitzmann
1,740512
I guess the BigQuery way would be using sub-selects on the array:
WITH t AS (SELECT * FROM UNNEST([
struct(['name:apple','color:red'] AS fruit),
struct(['name:pear','color:purple'] AS fruit)
]) )
SELECT
(SELECT SPLIT(f, ':')[SAFE_OFFSET(1)] FROM t.fruit f WHERE SPLIT(f, ':')[SAFE_OFFSET(0)]='name') AS name,
(SELECT SPLIT(f, ':')[SAFE_OFFSET(1)] FROM t.fruit f WHERE SPLIT(f, ':')[SAFE_OFFSET(0)]='color') AS color
FROM t
answered Nov 22 '18 at 10:02
Martin WeitzmannMartin Weitzmann
1,740512
edited Nov 22 '18 at 10:13
answered Nov 22 '18 at 10:02
Martin WeitzmannMartin Weitzmann
1,740512
answered Nov 22 '18 at 10:02
Martin WeitzmannMartin Weitzmann
1,740512
answered Nov 22 '18 at 10:02
Martin WeitzmannMartin Weitzmann
1,740512
1,740512
Thanks for the response, please see my updates.
– Shlomi Schwartz
Nov 22 '18 at 10:14
1
That's not possible due to the nature of these data structures: an array has a dynamic quantity of elements, while structs or tables have elements with keys whose schema need to stay consistent over multiple rows
– Martin Weitzmann
Nov 22 '18 at 10:18
add a comment |
Thanks for the response, please see my updates.
– Shlomi Schwartz
Nov 22 '18 at 10:14
1
That's not possible due to the nature of these data structures: an array has a dynamic quantity of elements, while structs or tables have elements with keys whose schema need to stay consistent over multiple rows
– Martin Weitzmann
Nov 22 '18 at 10:18
Thanks for the response, please see my updates.
– Shlomi Schwartz
Nov 22 '18 at 10:14
Thanks for the response, please see my updates.
– Shlomi Schwartz
Nov 22 '18 at 10:14
1
1
That's not possible due to the nature of these data structures: an array has a dynamic quantity of elements, while structs or tables have elements with keys whose schema need to stay consistent over multiple rows
– Martin Weitzmann
Nov 22 '18 at 10:18
That's not possible due to the nature of these data structures: an array has a dynamic quantity of elements, while structs or tables have elements with keys whose schema need to stay consistent over multiple rows
– Martin Weitzmann
Nov 22 '18 at 10:18
add a comment |
0
Not sure if there's a more succinct way to do this but this works
WITH CTE AS (
SELECT ["name:apple", "color:red"] as fruit
UNION ALL
SELECT ["name:pear", "color:green"]
),
CTE2 AS (
SELECT row_number() over () as rowNumber, fruit
FROM CTE
)
SELECT max(if(REGEXP_CONTAINS(fruit,'name:'),replace(fruit,'name:',''),null)) name,
max(if(REGEXP_CONTAINS(fruit,'color:'),replace(fruit,'color:',''),null)) color
FROM CTE2,
UNNEST(fruit) as fruit
GROUP BY rowNumber
answered Nov 22 '18 at 9:50
BobbylankBobbylank
1,038211
Thanks for the response, please see my updates.
– Shlomi Schwartz
Nov 22 '18 at 10:14
add a comment |
0
Not sure if there's a more succinct way to do this but this works
WITH CTE AS (
SELECT ["name:apple", "color:red"] as fruit
UNION ALL
SELECT ["name:pear", "color:green"]
),
CTE2 AS (
SELECT row_number() over () as rowNumber, fruit
FROM CTE
)
SELECT max(if(REGEXP_CONTAINS(fruit,'name:'),replace(fruit,'name:',''),null)) name,
max(if(REGEXP_CONTAINS(fruit,'color:'),replace(fruit,'color:',''),null)) color
FROM CTE2,
UNNEST(fruit) as fruit
GROUP BY rowNumber
answered Nov 22 '18 at 9:50
BobbylankBobbylank
1,038211
Thanks for the response, please see my updates.
– Shlomi Schwartz
Nov 22 '18 at 10:14
add a comment |
0
0
0
Not sure if there's a more succinct way to do this but this works
WITH CTE AS (
SELECT ["name:apple", "color:red"] as fruit
UNION ALL
SELECT ["name:pear", "color:green"]
),
CTE2 AS (
SELECT row_number() over () as rowNumber, fruit
FROM CTE
)
SELECT max(if(REGEXP_CONTAINS(fruit,'name:'),replace(fruit,'name:',''),null)) name,
max(if(REGEXP_CONTAINS(fruit,'color:'),replace(fruit,'color:',''),null)) color
FROM CTE2,
UNNEST(fruit) as fruit
GROUP BY rowNumber
answered Nov 22 '18 at 9:50
BobbylankBobbylank
1,038211
Not sure if there's a more succinct way to do this but this works
WITH CTE AS (
SELECT ["name:apple", "color:red"] as fruit
UNION ALL
SELECT ["name:pear", "color:green"]
),
CTE2 AS (
SELECT row_number() over () as rowNumber, fruit
FROM CTE
)
SELECT max(if(REGEXP_CONTAINS(fruit,'name:'),replace(fruit,'name:',''),null)) name,
max(if(REGEXP_CONTAINS(fruit,'color:'),replace(fruit,'color:',''),null)) color
FROM CTE2,
UNNEST(fruit) as fruit
GROUP BY rowNumber
answered Nov 22 '18 at 9:50
BobbylankBobbylank
1,038211
answered Nov 22 '18 at 9:50
BobbylankBobbylank
1,038211
answered Nov 22 '18 at 9:50
BobbylankBobbylank
1,038211
answered Nov 22 '18 at 9:50
BobbylankBobbylank
1,038211
1,038211
Thanks for the response, please see my updates.
– Shlomi Schwartz
Nov 22 '18 at 10:14
add a comment |
Thanks for the response, please see my updates.
– Shlomi Schwartz
Nov 22 '18 at 10:14
Thanks for the response, please see my updates.
– Shlomi Schwartz
Nov 22 '18 at 10:14
Thanks for the response, please see my updates.
– Shlomi Schwartz
Nov 22 '18 at 10:14
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53427308%2fbigquery-transpose-array-into-columns%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
You'd need to write a query which writes a query... See @MikhailBerlyant's answer here stackoverflow.com/questions/39394111/…
– Bobbylank
Nov 22 '18 at 10:40
Please consider to accept the answer that you find most useful.
– MonicaPC
Nov 30 '18 at 0:30