with - postgresql jsonb example

¿Acoplar pares clave/valor agregados de un campo JSONB?

Question

Estoy trabajando en Postgres 9.4 con la siguiente tabla:

 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 │

Tengo la siguiente consulta:

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;

Me da resultados con un objeto JSON adjunto a star_pu :

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

En cambio, me gustaría aplanar el resultado JSON a una serie de claves de espacios de nombres, por lo que el resultado se ve así:

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

es posible?

Answer 1

Este caso particular

La siguiente función crea dinámicamente una vista basada en una tabla:

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

Primero, cree una tabla a partir de su consulta.

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;

Luego, use la función, que creará una vista con el nombre de la tabla con _view postfix:

select create_totals_view(''totals'');

Finalmente, consulta la vista:

select * from totals_view;

Solución generalizada (para 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)