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

这种特殊情况

下面的函数基于表动态创建视图：

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

首先，从查询中创建一个表。

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

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)