Postgres: appiattisce le coppie chiave / valore aggregate da un campo JSONB?

Sto lavorando su Postgres 9.4 con la seguente tabella:

Column │ Type │ Modifiers ─────────────────┼──────────────────────┼────────────────────── id │ integer │ not null default practice_id │ character varying(6) │ not null date │ date │ not null pct_id │ character varying(3) │ total_list_size │ double precision │ not null star_pu │ jsonb 

Ho la seguente domanda:

 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; 

Mi dà risultati con un object JSON collegato a star_pu :

 date │ 2013-04-01 total_list_size │ 56025123.000000000000 star_pu │ { "antidepressants_cost" : 180102416.8036909901975399, "antiepileptic_drugs_cost" : 296228344.171576079922216... } 

Invece vorrei appiattire il risultato JSON su una serie di tasti con nomi, quindi il risultato è simile a questo:

 date │ 2013-04-01 total_list_size │ 56025123.000000000000 star_pu.antidepressants_cost │ 180102416.8036909901975399 star_pu.antiepileptic_drugs_cost │ 296228344.171576079922216 ... 

È ansible?

    Questo caso particolare

    La funzione seguente crea dynamicmente una vista basata su una tabella:

     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 $$; 

    Innanzitutto, crea una tabella dalla tua 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; 

    Successivamente, usa la funzione, che creerà una vista chiamata dopo la tabella con _view postfix:

     select create_totals_view('totals'); 

    Infine, interroga la visualizzazione:

     select * from totals_view; 

    Soluzione generalizzata (per 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 $$; 

    Uso:

     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)