JSONB to table in Postgres

Question

I'm new and still learning.

I have a table in jsonB format:

And I need to output be like:

Is there a easy way to do this. The jsonb has around 100 key. So it would be converted to 100 columns.

Is there a way to do this dynamically. I mean, if the number of columns or column name change, it´s not necessary to update the query.

Do you have a table with the same columns? That would make it easy. — Laurenz Albe
– Laurenz Albe, Commented May 26, 2020 at 8:04

Joao Henrique Ferreira · Accepted Answer · 2020-05-26 05:35:19Z

1

No,

For each key you need to create a column. To get all keys listed you can use this command:

select 
    jsonb_object_keys(u) 
from YOURTABLE;

After get the list of keys, I suggest you to use a text editor, to make easier the process. See this post certainly will help you.

After created your template you will need to do something like this:

SELECT 
   id, 
   u->'prb0' as prb0,
   u->'prb1' as prb1,
   u->'prb1' as prb2,
   ....
FROM YOUR_TABLE;

I strongly recommend you to create an intermediate table to store this query, and make it easier for furthers queries.

answered May 26, 2020 at 5:35

879 bronze badges

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

2 Comments

Henriqe Ferreira - Thanks alot. It worked. I created the query with all the 100 keys as you mentioned

I'm glad I helped :)