I need to create an script using python in this case to take a column jsonb of one table an create another table where the columns are all possible keys of this of this json.
For instance:
From
id | optional
1 | {"a":"4", "b":"5"}
2 | {}
3 | {"a":"8", "c":"9", "d":"9"}
to
id | a | b | c | d
1 | 4 | 5 | |
3 | 8 | | 9 | 9
I got the keys using this query:
select jsonb_object_keys(optional) as key from table group by key
I use the following code in python to create a table with keys as columns
connection = psycopg2.connect( host=host, database=database, user=user, password=password)
try:
columns = "("
for column in keys:
columns+=column+" TEXT "+','
columns = columns[0:len(columns)-1]
columns += ");"
query = "CREATE TABLE " + table +" "
query += columns
print query
cur = connection.cursor()
cur.execute(query)
connection.commit()
cur.close()
and I got the data that I need to put in the other table using this query:
select id, optional->'a',... from table where optional<>'{}'
In my case I have around 31 keys so the query above is big and on other hand if I want to reuse this script to another case I need to change this query probably.
So I would like to know if there are another way more elegant and more generic to do that. Even it is not necessary that the solution uses python if it is only with postgres it is good for me too
Any idea?
Thanks in advance
You may be insterested in Postgres solution described in this answer (see Generalized solution).
Example source table:
drop table if exists my_table;
create table my_table(id int primary key, data jsonb);
insert into my_table values
(1, '{"a":"4", "b":"5"}'),
(2, '{}'),
(3, '{"a":"8", "c":"9", "d":"9"}');
Using the function:
select create_jsonb_flat_view('my_table', 'id', 'data');
select * from my_table_view;
id | a | b | c | d
----+---+---+---+---
1 | 4 | 5 | |
2 | | | |
3 | 8 | | 9 | 9
(3 rows)
You can create a new table based on the flat view:
create table my_new_table as
select *
from my_table_view
order by id;