user3410249 user3410249 - 3 months ago 13
JSON Question

How to parse JSON Array to update record in postgreSQL

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

Answer

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.