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 · Accepted Answer · 2021-04-08 18:52:52Z

2

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;

edited Apr 8, 2021 at 18:52

answered Apr 8, 2021 at 18:23

eshirvana

24.7k3 gold badges28 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

fraoudas Over a year ago

This command results in an out of memory error and when I limit to 1000 for example, it doesn't add anything to the new table. select count(*) returns 0 rows. I do have the new table with all the columns already created though.

Collectives™ on Stack Overflow

How to create new table with multiple columns from json table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related