BigQuery grouping by array and keeping array structure-CodePudding

Is there a neat way of grouping by arrays and aggregating across other columns in BigQuery, while keeping the array structure?

I have this table

and I want the below result

I realise I can GROUP BY id and array_to_string(product,',') and then SUM over val, but I would like to keep the array structure in the product column.

I've tried

select id, product, sum(val) as val
from my_table
group by id, product

but it seems that arrays cannot be used in a GROUP BY.

This doesn't seem to work either

select id, array(select val from unnest(split(array_to_string(product,','))) val) as product, sum(val) as val
from my_table
group by id, array_to_string(product,',')

the array_to_string(product,',') in the select statement is not recognised to be grouped/aggregated

CodePudding user response：

You might consider below approach.

WITH sample_data AS (
  SELECT 2 id, [3] product, 5 val UNION ALL
  SELECT 11, [1, 2], 35 UNION ALL
  SELECT 11, [1, 2], 8 UNION ALL
  SELECT 11, [1], 40
)
SELECT ANY_VALUE(STRUCT(id, product)).*, SUM(val) AS val
  FROM sample_data
 GROUP BY FORMAT('%t', (id, product));

Query results