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, May 26 '20 at 08:04
Does [this](https://postgresql.verite.pro/blog/2018/06/19/crosstab-pivot.html) answer your question ? — Barbaros Özhan, May 26 '20 at 08:58

score 1 · Accepted Answer · answered May 26 '20 at 05:35

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 '20 at 05:35

Henriqe Ferreira - Thanks alot. It worked. I created the query with all the 100 keys as you mentioned – Breno1982 May 27 '20 at 02:55
I'm glad I helped :) – Joao Henrique Ferreira May 27 '20 at 22:49

1 Answers1