Convert JSON records in PostgreSQL into a Table

Question

I have a table_1 in PostgreSQL with column_1 and several records in the column containing a nested json with the following structure:

{"parent_1": {
    "child_1": [10.477058081076123, 12.570963520289965, 11.74866851427825],
    "child_2": [14.174190848828983, 19.3920283059595, 17.6712937162821]
},
"parent_2": {
    "child_1_1": [24.100638151071383, 28.544734824158617, 26.83283592992511],
    "child_1_2": [14.466083025027984, 34.788137217452125, 19.018732389073737]
} }

I want to convert the json record into another table so that I can import it as a customSQL including the arrays into Tableau.

EDIT 1:

This is the query I am trying:

 SELECT * , table_1.column_1 -> 'parent_1' ->  json_object_keys((table_1.column1 ->> 'parent_1')::json) FROM  public.table_1

EDIT 2:

As an output I would like to get a Table per Parent to be read as such in Tableau. In each table I would like to have:

Table: parent_1

Childs  | Value
----------------------------
child_1   | 10.477058081076123
child_1   | 12.570963520289965
child_1   | 11.74866851427825
child_2   | 14.174190848828983
child_2   | 19.3920283059595
child_2   | 17.6712937162821

SELECT * , table_1.column_1 -> 'parent_1' -> json_object_keys((table_1.column1 ->> 'parent_1')::json) FROM public.table_1 — Noque
Thanks, mlinth , I have edited the question to make it more clear. Is it a very basic question or is there another reason why it is getting so many negatives? — Noque
Well I think it's because without the SQL, it looked like you hadn't made any effort at an answer. You could further improve the question by adding a table showing your desired output... — mlinth

a_horse_with_no_name a_horse_with_no_name · Accepted Answer · 2017-10-25T11:31:40

You need to first unnest the inner structure of each "parent", and then unnest the array for each element.

For the first table you could do something like this:

create table parent_1
as
select p.k as child, e.v as value
from table_1
     cross join lateral jsonb_each(column_1 -> 'parent_1') as p(k,v)
     cross join lateral jsonb_array_elements(p.v) as e(v);

To do the same thing for the second table, replace the column_1 -> 'parent_1' with column_1 -> 'parent_2'

Convert JSON records in PostgreSQL into a Table

1 Answers