Array of Json into rows + postgresql + node.js

Question

Consider i have an array of json as follows

[
{"a":1,"b":2},{"a":3,"b":4} ,{"a":5,"b":6}
]

I have to insert this into postgtreSQL as follows:

in  out
1  2
3  4
5  6

I read about JSON datatypes in postgreSQL, but i couldn't figure out how to achieve this. please share your ideas. Thanks in advance.

Can you please tell, from where the JSON data is coming? or its always 'a' and 'b' as key? — sForSujit
@sForSujit JSON parser is for parsing the JSON , but my question is how can i convert this array of JSON into rows in postgresql. — Subburaj

Clodoaldo Neto Clodoaldo Neto · Accepted Answer · 2017-07-27T08:56:06

insert into my_table (in, out)
select a, b
from jsonb_to_recordset(
    '[{"a":1,"b":2},{"a":3,"b":4} ,{"a":5,"b":6}]'
) r (a int, b int)

https://www.postgresql.org/docs/current/static/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE

In Javascript escape the single quotes:

var query = 'insert into table (enroll_id, time) select enroll_id, time from jsonb_to_recordset(\'' + data + '\') r (enroll_id bigserial, time timestamp)';

Array of Json into rows + postgresql + node.js

1 Answers