展平来自JSONB字段的聚合键/值对? [英] Flatten aggregated key/value pairs from a JSONB field?
本文介绍了展平来自JSONB字段的聚合键/值对?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在使用下表的Postgres 9.4中工作:
I am working in Postgres 9.4 with the following table:
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 │
我有以下查询:
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;
它为我提供了带有star_pu
附加的JSON对象的结果:
It gives me results with a JSON object attached to star_pu
:
date │ 2013-04-01
total_list_size │ 56025123.000000000000
star_pu │ { "antidepressants_cost" : 180102416.8036909901975399, "antiepileptic_drugs_cost" : 296228344.171576079922216... }
相反,我想将JSON结果展平为一系列命名空间的键,因此结果如下所示:
Instead I would like to flatten the JSON result to a series of namespaced keys, so the result looks like this:
date │ 2013-04-01
total_list_size │ 56025123.000000000000
star_pu.antidepressants_cost │ 180102416.8036909901975399
star_pu.antiepileptic_drugs_cost │ 296228344.171576079922216
...
这可能吗?
推荐答案
这种特殊情况
下面的函数基于表动态创建视图:
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;
接下来,使用该函数,该函数将创建一个以_view
后缀命名的以表命名的视图:
Next, use the function, which will create a view named after the table with _view
postfix:
select create_totals_view('totals');
最后,查询视图:
select * from totals_view;
通用解决方案(用于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 $$;
用法:
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)
这篇关于展平来自JSONB字段的聚合键/值对?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文