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 http://yetanotherdataminer.blogspot.co.uk/2013/04/how-to-get-json-data-type-into.html which gives details on how to load JSON data into a single string per row, then transform? — mc110, Jun 26 '14 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 '14 at 13:46
@mc110: i am using postgres 9.1.2 which doesnt support this functionality — Dinesh Ravi, Jun 26 '14 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 '14 at 15:41
And where occurs the encoding in json ? javascript in browser ? servlet ? jsp ? Or is it only to allow a rest API ? — Serge Ballesta, Jun 26 '14 at 15:58
@SergeBallesta: Encoding into json occurs in a function toJson() to which a call from servlet is made in doGet() function. — Dinesh Ravi, Jun 26 '14 at 16:20

score 6 · Accepted Answer · edited May 23 '17 at 12:11

6

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.

edited May 23 '17 at 12:11

Community

answered Jun 26 '14 at 14:12

pozs

1 Answers1