Postgres 11

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t (id int, v1 int, v2 int, v3 int);
insert into t values
(1, 10, 3, null),
(2, 5,null,1 ),
(3, null,2, 7 );

3 rows affected

TABLE t;


id
v1
v2
v3


1
10
3
null

2
5
null
1

3
null
2
7


SELECT id, json_agg(j1) AS j
FROM  (SELECT id, to_jsonb(t) - 'id' AS j1 FROM t) t1
GROUP  BY 1;


id
j


3
[{"v1": null, "v2": 2, "v3": 7}]

2
[{"v1": 5, "v2": null, "v3": 1}]

1
[{"v1": 10, "v2": 3, "v3": null}]


WITH base AS (
   SELECT id, jsonb_agg(jsonb_build_object('v1',v1,'v2',v2,'v3',v3)) AS j
   FROM   t
   GROUP  BY id
   )
SELECT sum((x->>'v1')::int) AS v1_sum
FROM   base, jsonb_array_elements(j) t(x);


v1_sum


15

id	j
3	[{"v1": null, "v2": 2, "v3": 7}]
2	[{"v1": 5, "v2": null, "v3": 1}]
1	[{"v1": 10, "v2": 3, "v3": null}]

id	v1	v2	v3
1	10	3	null
2	5	null	1
3	null	2	7