How to extract values from a json to insert into a table in Postgresql

Asked

Viewed 448 times

1

I need to migrate from sql server 2017 to the postgresql,but I never used postgresql and I’m having a hard time. I need to convert a array json for values that are inserted in the table.

In the sql server I used to use a insert with select openjson and the clause with to specify what I would take from json.

valores json := '[
    {"tipo":"a","valor:"10.89}
    {"tipo":"b","valor:"10.88}
    {"tipo":"c","valor:"10.87}
    {"tipo":"d","valor:"10.86}
]';
insert into dbo.testedojson(valor,tipo)
select * 
from json_populate_record(NULL::dbo.testedojson,valores);

I took this example from internet to take a test,dbo.testedojson is the table that will receive the values but there is a syntax error that does not find.

postgresql

1 answer

2


According to the documentation of Postgres 9.5 you can use the function json_populate_recordset

Expands the outermost array of Objects in from_json to a set of Rows Whose Columns match the record type defined by base (see note Below).

Expand from_json’s most external object array to a set of rows whose columns correspond to the type of record defined by the base (see note below).

Example taken from Git hub - bwestergard user - goes below:

INSERT INTO foo SELECT * from json_populate_recordset(null::foo,'[ 
{
   "id":0,
   "a":"harold",
   "b":"wilson",
   "c":"j"
},
{
   "id":1,
   "a":"brian",
   "b":"wilson",
   "c":"q"
},
{
   "id":2,
   "a":"jack",
   "b":"kemp",
   "c":"q"
} ]'); 
COMMIT;

Browser other questions tagged postgresql

You are not signed in. Login or sign up in order to post.