Unnest array of integers SQL BigQuery-CodePudding

I cannot seem to find anything that helps with unnesting a list of integers in SQL BigQuery. I've tried using select * from my_table, unnest(column) as column but I get this error:
Values referenced in UNNEST must be arrays. UNNEST contains expression of type STRUCT<os STRING, product_ids STRING...

|Product IDs|
|[123456,234567,345678,456789]|
|[987654,876543,765432,654321]|

I basically want to to get it so that I just have each product number on a separate line. So...
|Product IDs|
|123456|
|234567|
|345678|
|456789|
|987654|
|876543|
|765432|
|654321|

CodePudding user response：

Based on the error message it seems like there are additional fields in your struct. To unnest you will need to isolate the array, try the following:

with sample_data as (
  select [STRUCT('linux' as os, [123456,234567,345678,456789] as product_id),
          STRUCT('macos' as os, [987654,876543,765432,654321] as product_id)] as values
)

select pid
from sample_data,
UNNEST(values) v,
UNNEST(v.product_id) pid

CodePudding user response：

Giving you 4 solutions, see which one fits your case:

-- Solution1  
select product_id from 
(select "123,234,345,456,456,678,789" as product_ids),unnest(split(product_ids)) as product_id


--Solution2
select product_id from 
(select struct("ios" as os, "123,234,345,456,456,678,789" as product_ids) as os_products), unnest(split(os_products.product_ids)) as product_id

--Solution3
select product_id from 
(select array_agg(os_products) as os_products from 
(select struct("ios" as os, "123,234,345,456,456,678,789" as product_ids) as os_products
union all 
select struct("android" as os, "abc,cde,efg" as product_ids) as os_products
)), unnest(os_products) as op, unnest(split(op.product_ids)) as product_id

--Solution4
select product_id from 
(select array_agg(os_products) as os_products from 
(select struct("ios" as os, split("123,234,345,456,456,678,789") as product_ids) as os_products
union all 
select struct("android" as os, split("abc,cde,efg") as product_ids) as os_products
)), unnest(os_products) as op, unnest((op.product_ids)) as product_id