Skip to content
Advertisement

Flatten a subarray in postgresql

Input:

postgres=> select sets from matches limit 2;

         sets         
----------------------
 {{6,4},{6,2}}
 {{6,3},{5,7},{10,4}}

(2 rows)

Expected (Sum of the value in each array)

postgres=> select sets from matches limit 2;

         sets         
----------------------
 18
 33

(2 rows)

I tried, but it only gives me the value of two first value in the first sub array:

postgres=> select unnest(sets) from matches limit 2;

 unnest 
--------
      6
      4

(2 rows)

Advertisement

Answer

You can use a scalar sub-select:

select (select sum(i)
        from unnest(sets) as t(i)) as sum
from matches;
User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement