Convert a json which is a list of dictionaries into column/row format in Postgresql

I´ve a json which is a list of dictionaries with the next syntax:

 [ 
     {
    "Date_and_Time": "Dec 29, 2017 15:35:37",
    "Componente": "Bar",
    "IP_Origen": "175.11.13.6",
    "IP_Destino": "81.18.119.864",
    "Country": "Brazil",
    "Age": "3"

},

{

    "Date_and_Time": "Dec 31, 2017 17:35:37",
    "Componente": "Foo",
    "IP_Origen": "176.11.13.6",
    "IP_Destino": "80.18.119.864",
    "Country": "France",
     'Id': '123456',
     'Car': 'Ferrari'

},
{
  "Date_and_Time": "Dec 31, 2017 17:35:37",
    "Age": "1",
    "Country": "France",
     'Id': '123456',
     'Car': 'Ferrari'
},
{
    "Date_and_Time": "Mar 31, 2018 14:35:37",
    "Componente": "Foo",
    "Country": "Germany",
     'Id': '2468',
     'Genre': 'Male'

}
]

The json is really big and each dictionary have different amount of key/values fields. And what I want to do is to create a table in postgresSQL where the key represents a column and the value a row. In the example explained above I would like table like this:

Date_and_Time          | Componente | IP_Origen   | IP_Destino    | Country| Id    | Car    | Age| Genre
         
Dec 29, 2017 15:35:37  | Bar        | 175.11.13.6 | 81.18.119.864 | Brazil |  -    |   -    | 3  | -
Dec 31, 2017 17:35:37  | Foo        | 176.11.13.6 | 80.18.119.864 | France |123456 |Ferrari | -  | -
Dec 31, 2017 17:35:37  | -          | -           |   -           | France |123456 |Ferrari | 1  | -
Mar 31, 2018 14:35:37  | Foo        |   -         |    -          | Germany| 2468  |    -   | -  | Male

The only solution I can think is putting the values one by one but this is no efficient at all

Answer

You can use jsonb_to_recordset to create record set out of your json and then use insert into to insert the records.

insert into table
select * from jsonb_to_recordset('<your json>'::jsonb)
as rec(Date_and_Time datetime, Componente text, IP_Origen text) --Specify all columns inside the table

Sample DBFiddle

Advertisement

Answer