BigQuery: flatten the table

Question

Is there any way that I can simply flatten the below table so that I can multiply (num*num_2) as mul and then later I can sum(mul) group by id. So really I am looking for table which is similar to second image shown below.

PS: If I unnest then the result is something like shown below which doesn't help me in what I would like to achieve

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2018-01-15T22:27:14

Below few options (BigQuery Standard SQL)

#standardSQL
WITH `yourproject.yourdataset.yourtable` AS (
  SELECT 1 id, [STRUCT<num INT64, num_2 INT64>(2013, 2013),(1625, 1625),(1297, 1297),(7634, 7634)] nums UNION ALL
  SELECT 2, [STRUCT<num INT64, num_2 INT64>(1,1),(2,2),(3,3),(4,4),(5,5)]
)
SELECT id, (SELECT SUM(num * num_2) FROM UNNEST(nums) nums ) as mul
FROM `yourproject.yourdataset.yourtable`

OR

#standardSQL
WITH `yourproject.yourdataset.yourtable` AS (
  SELECT 1 id, [STRUCT<num INT64, num_2 INT64>(2013, 2013),(1625, 1625),(1297, 1297),(7634, 7634)] nums UNION ALL
  SELECT 2, [STRUCT<num INT64, num_2 INT64>(1,1),(2,2),(3,3),(4,4),(5,5)]
)
SELECT id, SUM(num * num_2) AS mul
FROM `yourproject.yourdataset.yourtable`, UNNEST(nums) nums
GROUP BY id

both return below

id  mul  
1   66652959     
2   55

If you would not need do SUM(num * num_2) but rather just have flattened your table - you can use below example

#standardSQL
WITH `yourproject.yourdataset.yourtable` AS (
  SELECT 1 id, [STRUCT<num INT64, num_2 INT64>(2013, 2013),(1625, 1625),(1297, 1297),(7634, 7634)] nums UNION ALL
  SELECT 2, [STRUCT<num INT64, num_2 INT64>(1,1),(2,2),(3,3),(4,4),(5,5)]
)
SELECT id, num, num_2
FROM `yourproject.yourdataset.yourtable`, UNNEST(nums) nums

BigQuery: flatten the table

1 Answers