Unnest array of integers SQL BigQuery

borg620 0
Viewed 44

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|

EDIT: sorry forgot to add in - there is a customer id, then the product_ids has the list of product numbers.
So I want the customer_id, and the product ids on separate lines. enter image description here

so output for 1 customer to be like the below:
enter image description here

list google-bigquery sql arrays
2 Answers

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
Daniel Zagales0

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

========

Based on your latest re-edit of your question and having total 2 columns in following way: customer_id, array of product_ids

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

This will give you customer_id and product_id , 1 per each row.

userbE1j0
Related