Imaing we have data like this:
SQL
x
5
1
WITH Sequences AS
2
(SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS prod_type, [1.1, 1.2, 2.1, 2.3, 3.3, 3.4] AS prod_price,
3
UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS prod_type, [1.3, 4.2, 2.1, 7.3, 5.3, 9.4] AS prod_price,
4
UNION ALL SELECT 3 AS id, [5, 10] AS prod_type, [1.8, 4.9, 2.0, 7.6, 5.1, 8.4] AS prod_price)
5
select * from sequences

How could I get the total price of each “prod_type” for every “id”?
First we need to unfold the “prod_type” and “prod_price” correspondingly:
SQL
1
9
1
WITH Sequences AS
2
(SELECT 1 AS id, [0, 1, 1, 2, 3, 5] AS prod_type, [1.1, 1.2, 2.1, 2.3, 3.3, 3.4] AS prod_price,
3
UNION ALL SELECT 2 AS id, [2, 4, 8, 16, 32] AS prod_type, [1.3, 4.2, 2.1, 7.3, 5.3, 9.4] AS prod_price,
4
UNION ALL SELECT 3 AS id, [5, 10] AS prod_type, [1.8, 4.9, 2.0, 7.6, 5.1, 8.4] AS prod_price)
5
SELECT id, prod_type, prod_price
6
from
7
sequences,
8
unnest(prod_type) AS prod_type,
9
unnest(prod_price) AS prod_price;

and then use “group by” to calculate total price:
SQL
1
12
12
1
...
2
SELECT
3
id,
4
prod_type,
5
SUM(prod_price)
6
FROM
7
sequences,
8
UNNEST(prod_type) AS prod_type,
9
UNNEST(prod_price) AS prod_price
10
GROUP BY
11
id,
12
prod_type