Bigquery - Transpose Array into columns

Question

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;

You'd need to write a query which writes a query... See @MikhailBerlyant's answer here https://stackoverflow.com/questions/39394111/dynamically-creating-columns-from-row-data-using-select-in-bigquery — Bobbylank, Nov 22 '18 at 10:40

score 2 · Accepted Answer · answered Nov 22 '18 at 22:58

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

Martin Weitzmann · Answer 2 · 2018-11-22T10:13:04.410

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

edited Nov 22 '18 at 10:13

answered Nov 22 '18 at 10:02

Martin Weitzmann

4,430
10
19

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

score 0 · Answer 3 · answered Nov 22 '18 at 09:50

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

Bigquery - Transpose Array into columns

3 Answers3