展平来自JSONB字段的聚合键/值对？

Question

我正在使用下表的Postgres 9.4：

     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;

它为我提供了带有附加到JSON对象的结果star_pu：

date            │ 2013-04-01

total_list_size │ 56025123.000000000000

star_pu         │ { "antidepressants_cost" : 180102416.8036909901975399, "antiepileptic_drugs_cost" : 296228344.171576079922216... }

相反，我想将JSON结果展平为一系列命名空间的键，因此结果如下所示：

date            │ 2013-04-01

total_list_size │ 56025123.000000000000

star_pu.antidepressants_cost        │ 180102416.8036909901975399

star_pu.antiepileptic_drugs_cost    │ 296228344.171576079922216 

...

这可能吗？

Answer 1

到目前为止，这是我在堆栈溢出时见过的最有用，最深刻的答案。基于这里概述的技术，将jsonb字段展平到这样的视图中的一般功能应该成为postgresql的一部分，因为它解决了jsonb字段出现的各种问题。