Postgresql – How to import json table into PostgreSQL table

jsonpostgresql

I'm trying to import some tables into a PostgreSQL db that have been stored as json files. Example data:

[{
"token": "a978f9793a594d11860e9cf7962996d7",
"name": "vehicle.moving",
"description": "Vehicle is moving."
},
{
"token": "95e38bf08c2842f39f2d64ba8152b265",
"name": "vehicle.stopped",
"description": "Vehicle, with a driver/rider in/on it, is currently stationary but has an intent to move."
}]

Would like the result to look like:

token                            | name            | description
---------------------------------+-----------------+--------------------
a978f9793a594d11860e9cf7962996d7 | vehicle.moving  | Vehicle is moving.
95e38bf08c2842f39f2d64ba8152b265 | vehicle.stopped | Vehicle, with a driver/rider in/on it, is currently stationary but has an intent to move.

My SQL currently looks like this:

\set content `type "F:\Datasets\attribute.json"`
insert into attribute select * from jsonb_array_elements(:'content');

Which just adds rows of quoted json to my table.
Have been bashing at this all day (SQL rookie) and would be very grateful for help.

Best Answer

That's what I would do:

SELECT v.*
FROM t
     ,jsonb_to_recordset(t.j::jsonb) AS v(token text, name text, description text);

Or in a 2-step query:

SELECT vr.*
FROM t
     ,jsonb_array_elements(j::jsonb) AS tt(recs)
     ,jsonb_to_record(tt.recs::jsonb) AS vr(token text, name text, description text);

source @dba.stex

dbfiddle

Related Question