How do I extract values from a json to insert into a table in PostgreSQL

Question

How do I extract values from a json to insert into a table in PostgreSQL

Navigation

#1 by (1 votes)

1

I need to migrate from sql server 2017 to postgreSQL , but I have never used postgreSQL and am having difficulty. I need to convert an array json to values that are inserted into the table.

In sql server I used a insert with select openjson and the with clause to specify what I would get 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've taken this example in internet to do a test, dbo.testedojson is the table that will receive the values however there is a syntax error that I can not find.

postgresql

asked by anonymous 23.01.2018 / 19:51

1 answer

"Col is undefined" when using table.rows.add How to get other values from the selected Spinner item in addition to that shown?

score 1 · Accepted Answer

___ erkimt ___ How do I extract values from a json to insert into a table in PostgreSQL ______ qstntxt ___

I need to migrate from sql server 2017 to postgreSQL , but I have never used postgreSQL and am having difficulty. I need to convert an array json to values that are inserted into the table.

In sql server I used a %code% with %code% and the %code% clause to specify what I would get from json .

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;

I've taken this example in internet to do a test, %code% is the table that will receive the values however there is a syntax error that I can not find.

______ ___ azszpr271385

According to the Postgres 9.5 documentation you can use the json_populate_recordset

Expands the outermost from_json array of objects in a set of rows to columns Whose match record type defined by the base (see note below).

expands the array of outer objects from_json for a set of lines whose columns correspond to the record type   defined by the base (see note below).

Git hub - bwestergard user - is as follows:

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;

___