I have a table of attributes, one of them containing a json array of 11 numeric elements. I need to do some aggregation (nothing fancy, just averages, max/min stats, etc) on the data in the json array.
The generalized answer found here looks perfect for reformatting the data into a temporary view for quick queries. I changed jsonb_each to son_each (maybe this would work?) and return no error, but end up with a single column titled 'create_jsonb_pivot_view' with no data.
Here is the function:
create or replace function create_jsonb_pivot_view
(table_name text, regular_columns text, json_column text)
returns void language plpgsql as $$
declare
s text;
begin
execute format ($fmt$
select string_agg(format('%s->>''%s'' "%s"', key, key), ',')
from (
select distinct key
from %s, json_each(%s)
order by 1
) s;
$fmt$, json_column, '%s', '%s', table_name, json_column)
into s;
execute format('
drop view if exists %s_view;
create view %s_view as
select %s, %s from %s',
table_name, table_name, regular_columns, s, table_name);
end $$;
And lastly the query that uses the function:
select create_jsonb_pivot_view('table', 'A, B, C, D', 'params')
I've never used functions in SQL before so this is some new territory for me. Perhaps there's a different and better way to achieve my goal all together?
