Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_a(
   id bigint NOT NULL,
   scores jsonb,
   CONSTRAINT table_a_pkey PRIMARY KEY (id)
);

INSERT INTO table_a(
id, scores)
VALUES (1, '{"a":20,"b":20}');

1 rows affected

CREATE OR REPLACE FUNCTION example(
   table_name text,
   regular_columns text,
   json_column text,
   view_name text
  ) RETURNS text
    LANGUAGE plpgsql AS
$func$
DECLARE
   cols TEXT;
   cols_sum TEXT;
BEGIN
   EXECUTE
      format(
         $ex$SELECT string_agg(
                       format(
                          'CAST(%2$s->>%%1$L AS INTEGER) AS %%1$I',
                          key),
                       ', '
                    )
             FROM (SELECT DISTINCT key
                   FROM %1$s, jsonb_each(%2$s)
                   ORDER BY 1
                  ) s;$ex$,
         table_name, json_column
      )
   INTO cols;

   EXECUTE
      format(
         $ex$SELECT string_agg(
                       format(
                          'CAST(%2$s->>%%1$L AS INTEGER)',
                          key
                       ),
                       '+'
                    )
             FROM (SELECT DISTINCT key
                   FROM %1$s, jsonb_each(%2$s)
                   ORDER BY 1) s;$ex$,
         table_name, json_column
      )
   INTO cols_sum;

   EXECUTE
      format(
         $ex$DROP VIEW IF EXISTS %2$s;
             CREATE VIEW %2$s AS
             SELECT %3$s, %4$s, SUM(%5$s) AS total
             FROM %1$s
             GROUP BY %3$s$ex$,
         table_name, view_name, regular_columns, cols, cols_sum
      );

   RETURN cols;
END
$func$; 
SELECT example(
   'table_a',
   ' id',
   'scores',
   'view_table_a'
);


example


CAST(scores->>'a' AS INTEGER) AS a, CAST(scores->>'b' AS INTEGER) AS b


TABLE view_table_a;


id
a
b
total


1
20
20
40