-
-
Save StanisLove/264ea16806c7d3f916008a2a06c0167f to your computer and use it in GitHub Desktop.
Flattening json data in PostgreSQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 $$; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment