Flatten aggregated key/value pairs from a JSONB field?

This particular case

The function below dynamically creates a view based on a table:

create or replace function create_totals_view(table_name text)returns void language plpgsql as $$declare    s text;begin    execute format ($fmt$        select string_agg(format('star_pu->>''%s'' "%s"', key, key), ',')        from (            select distinct key            from %s, json_each(star_pu)            order by 1            ) s;        $fmt$, '%s', '%s', table_name)    into s;    execute format('        drop view if exists %s_view;        create view %s_view as         select date, total_list_size, %s from %s',         table_name, table_name, s, table_name);end $$;

First, create a table from your query.

create table totals as    SELECT date,           AVG(total_list_size) AS total_list_size,           json_object_agg(key, val) AS star_pu    FROM (SELECT date,                 SUM(total_list_size) AS total_list_size,                 key, SUM(value::numeric) val FROM frontend_practicelist p,                 jsonb_each_text(star_pu)           GROUP BY date, key ) p    GROUP BY date    ORDER BY date;

Next, use the function, which will create a view named after the table with _view postfix:

select create_totals_view('totals');

Finally, query the view:

select * from totals_view;

Generalized solution (for jsonb)

create or replace function create_jsonb_flat_view    (table_name text, regular_columns text, json_column text)    returns text language plpgsql as $$declare    cols text;begin    execute format ($ex$        select string_agg(format('%2$s->>%%1$L "%%1$s"', 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$        drop view if exists %1$s_view;        create view %1$s_view as         select %2$s, %3$s from %1$s        $ex$, table_name, regular_columns, cols);    return cols;end $$;

Usage:

create table example (id int, name text, params jsonb);insert into example values(1, 'Anna', '{"height": 175, "weight": 55}'),(2, 'Bob', '{"age": 22, "height": 188}'),(3, 'Cindy', '{"age": 25, "weight": 48, "pretty": true}');select create_jsonb_flat_view('example', 'id, name', 'params');select * from example_view; id | name  | age | height | pretty | weight ----+-------+-----+--------+--------+--------  1 | Anna  |     | 175    |        | 55  2 | Bob   | 22  | 188    |        |   3 | Cindy | 25  |        | true   | 48(3 rows)