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.

• mc110: 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: 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.
• Serge Ballesta: And ... what have you tried and where are you stuck ?
• Dinesh Ravi: @mc110: i am using postgres 9.1.2 which doesnt support this functionality
• Dinesh Ravi: @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

Answer 1

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.