Created
February 19, 2019 12:49
-
-
Save imamdigmi/4f4241ad4f746dbe1cd41ddf7d1fe511 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
Original post on Medium by Marc Laforet