How to parse JSON Array to update record in postgreSQL

Question

I have a Table name called employee_Details with Columns

 EMP_ID   NAME  VECH_NO  SALARY
   1       A      1234    100
   2       B      12345   200

I Construct JSON array using Java. with structure

{["EMP_ID":1,"NAME":Y,"VECH_NO":4587,"SALARY":1500],["EMP_ID":3,"NAME":Z,"VECH_NO":4007,"SALARY":1800]}

I need to update full record where EMP_ID exists on table else insert as new record. There is any in build function available in postgreSQL to achieve this. I refer this https://www.postgresql.org/docs/9.3/static/functions-json.html but didn't get solution

I have a JSON array. I have to Update if exists else insert it by using any inbuild functions like "json_populate_recordset". — user3410249
Seems that you swapped [] and {} in your example. And what version of PostgreSQL are you using? If before 9.5, can you upgrade (to use new upsert capabilities)? — MatheusOl

MatheusOl MatheusOl · Accepted Answer · 2016-09-08T11:46:58

First of all, your JSON is malformed, I guess the intended was:

[
    {"EMP_ID":1,"NAME":"Y","VECH_NO":4587,"SALARY":1500},
    {"EMP_ID":3,"NAME":"Z","VECH_NO":4007,"SALARY":1800}
]

Assuming the following table definition:

CREATE TEMP TABLE employee_details(
    "EMP_ID" integer primary key,
    "NAME" text,
    "VECH_NO" integer,
    "SALARY" numeric
);

And a sample data (just to show the update):

INSERT INTO employee_details VALUES(1, 'X', 123, 123);

You can first use json_array_elements to make each array one row and json_populate_record to get each value as the original table type, like this:

SELECT r.*
FROM
    json_array_elements('[{"EMP_ID":1,"NAME":"Y","VECH_NO":4587,"SALARY":1500},{"EMP_ID":3,"NAME":"Z","VECH_NO":4007,"SALARY":1800}]') AS a(element),
    json_populate_record(NULL::employee_details, a.element) AS r;

With that, you can simple use INSERT ... ON CONFLICT UPDATE:

INSERT INTO employee_details("EMP_ID", "NAME", "VECH_NO", "SALARY")
(
    SELECT r."EMP_ID", r."NAME", r."VECH_NO", r."SALARY"
    FROM
        json_array_elements('[{"EMP_ID":1,"NAME":"Y","VECH_NO":4587,"SALARY":1500},{"EMP_ID":3,"NAME":"Z","VECH_NO":4007,"SALARY":1800}]') AS a(element),
        json_populate_record(NULL::employee_details, a.element) AS r
)
ON CONFLICT ("EMP_ID") DO
UPDATE SET
    "NAME" = EXCLUDED."NAME",
    "VECH_NO" = EXCLUDED."VECH_NO",
    "SALARY" = EXCLUDED."SALARY"
;

The ON CONFLICT clause only works on version 9.5 or higher. Before that you have to use some tricks with loop and retry or writable common table expression (although that has race-condition issues); in any case it is a good reason to upgrade if you are on older versions.

How to parse JSON Array to update record in postgreSQL

1 Answers