2

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.

Subburaj
  • 5,114
  • 10
  • 44
  • 87

1 Answers1

0
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)';
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • Thanks.. but if i construct a query like `var query = 'insert into table (enroll_id, time) select EnrollNumber, time from jsonb_to_recordset( '+data+') r (enroll_id bigserial, time timestamp)';` Here data is the json Array but its not a valid query. how can i rectify this?? – Subburaj Jul 27 '17 at 09:15
  • @Subburaj Escape the single quotes. – Clodoaldo Neto Jul 27 '17 at 09:52
  • `{ error: column "enrollnumber" does not exist` throwing error – Subburaj Jul 27 '17 at 10:26
  • @Subburaj `select enroll_id` – Clodoaldo Neto Jul 27 '17 at 10:30
  • according to ur query `select enroll_id, time from jsonb_to_recordset` but in the JSON the key what we have is `EnrollNumber` then how it will select enroll_id from JSON set.. – Subburaj Jul 27 '17 at 10:36
  • enroll_id is not selected its empty in table, i think we have to change enroll_id to 'EnrollNumber' at the last part – Subburaj Jul 27 '17 at 10:44