How do I effectively summarize/aggregate these arrays in postgresql, given that I e.g. want to summarize all integers from index 2 to index 6, on column counts and COALESCE null or missing to 0?
table_x:
ID Counts
1 {1, 2, null, 0, null, 5, 8, 10}
2 {2, 5, 1, null, 3}
3 {null, 3, 5, 0, null, 8, 1}
To result:
Counts
{10,6,0,3,13}
You can use unnest to get each element, filter indexes from 2 to 6, calculate sum per each position and combine everything to one array using array_agg:
WITH cte AS
(
SELECT pos, SUM(val::int) AS val
FROM table_x
,LATERAL unnest(counts,ARRAY[1,2,3,4,5,6,7,8,9,10])
AS sub(val, pos)
WHERE pos BETWEEN 2 AND 6
GROUP BY pos
)
SELECT array_agg(val ORDER BY pos) AS result
FROM cte;
Output:
"{10,6,0,3,13}"
EDIT:
As @a_horse_with_no_name proposed in comment you could use unnest with ordinality:
If the WITH ORDINALITY clause is specified, an additional column of type bigint will be added to the function result columns. This column numbers the rows of the function result set, starting from 1.
WITH cte AS
(
SELECT pos, SUM(val::int) AS val
FROM table_x
,LATERAL unnest(counts) with ordinality AS sub(val, pos)
WHERE pos BETWEEN 2 AND 6
GROUP BY pos
)
SELECT array_agg(val ORDER BY pos)
FROM cte;
