How to insert json data into my table using postgres

Question

i have a clients table with 2 columns

CREATE TABLE clients
(
    client_id    serial primary key,
    name        VARCHAR(40) not null
)

i have a json data like

[{client_id:"1",name:"Rick"},{client_id:"2",name:"Carlin"}]

Now i need to use this json to parse and insert into my client table. How can i do it using jsp servlets and postgres database.

have you looked at yetanotherdataminer.blogspot.co.uk/2013/04/… which gives details on how to load JSON data into a single string per row, then transform? — mc110, Jun 26 at 13:44
Also, what version of postgres are you using, as 9.2 added support for JSON inside the DB, and 9.3 added extra functionality for dealing with JSON data. — mc110, Jun 26 at 13:46
@mc110: i am using postgres 9.1.2 which doesnt support this functionality — Dinesh Ravi, Jun 26 at 15:36
@SergeBallesta: Jsp page gets this data from client through form which converted into json and passed to the servlet. Now servlet has to insert the data into the database — Dinesh Ravi, Jun 26 at 15:41

pozs · Answer 1 · 2014-06-26 14:18:03Z

If you want to do it on PostgreSQL (9.3+) side, you can use the json_populate_recordset function:

insert into clients
select *
from json_populate_recordset(
  null::clients,
  '[{client_id:"1",name:"Rick"},{client_id:"2",name:"Carlin"}]'
)

Although, that's usually not a good idea to manually insert values to a serial column.

1 Answer 1