java - 如何解析 JSON 数组以更新 postgreSQL 中的记录

Question

我有一个名为 employee_Details 的表，其中包含列

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

我使用 Java 构建 JSON 数组。有结构

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

我需要更新表中存在 EMP_ID 的完整记录，否则作为新记录插入。 postgreSQL 中有任何可用的构建函数来实现这一点。我指的是这个 https://www.postgresql.org/docs/9.3/static/functions-json.html但没有得到解决方案

Answer 1

首先，您的 JSON 格式不正确，我想是这样的:

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

假设下表定义:

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

和示例数据(只是为了显示更新):

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

可以先用json_array_elements to make each array one row and json_populate_record to get each value as the original table type ，像这样:

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;

有了它，您可以简单地使用 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"
;

ON CONFLICT 子句仅适用于 9.5 或更高版本。在此之前，您必须使用 some tricks with loop and retry或可写公用表表达式(尽管存在竞争条件问题)；在任何情况下，如果您使用的是旧版本，这都是升级的好理由。