Flatten a subarray in postgresql

Question

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)

user330315 · Accepted Answer · 2020-09-23 20:36:14Z

2

You can use a scalar sub-select:

select (select sum(i)
        from unnest(sets) as t(i)) as sum
from matches;

answered Sep 23, 2020 at 20:36

user330315

Sign up to request clarification or add additional context in comments.

Comments

Michael G · Accepted Answer · 2020-09-23 20:34:58Z

0

I haven't worked with postgres in awhile but maybe you're missing the Sum part?:

SELECT
  sum(a) AS total
FROM
  (
    SELECT
      unnest(sets from matches) AS a
  ) AS b

answered Sep 23, 2020 at 20:34

Michael G

5824 silver badges12 bronze badges