How to create new table with multiple columns from json table

Question

I have a table filled with json objects. There is only one column 'data'.

I would like to convert this table into one with multiple columns for all the keys in the json objects.

For example,

Right now my table is like this:

data
{'key1': 'value1', 'key2': 'value2'}
{'key1': 'value3', 'key2': 'value4'}

But I would like it like this:

key1	key2
value1	value2
value3	value4

I'm not trying to query it in this way, I would like to completely create a new table in the format that I've shown above.

I can try running:

INSERT INTO new_table 
SELECT data -> 'key1', data -> 'key2' 
FROM old_table

But since my json objects have hundreds of columns this may be inefficient. Is there a more efficient way to do so? Any help or suggestions is appreciated.

eshirvana eshirvana · Accepted Answer · 2021-04-08T18:23:46

maybe this way is more simple?

select t.*
from yourtable
cross join json_to_record(jsoncol) t(key1 text,key2 text,key3 text,...);

on the second thought , if you already have a table that matches the columns from your json , you can do this:

create table newTable (key1 text, key2 text , ...);

select t.*
from yourtable
cross join json_populate_record(null::newTable,jsoncol) t;

How to create new table with multiple columns from json table

1 Answers