如何解析JSON Array以更新postgreSQL中的记录

Question

我有一个名为employee_Details列的表名称

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

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

{[“EMP\u ID”：1，“NAME”：Y，“VECH\u NO”：4587，“SALARY”：1500），“EMP\u ID”：3，“NAME”：Z，“VECH\u NO”：4007，“SALARY”：1800]}

我需要更新表上EMP\U 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\u array\u elements将每个数组设为一行，然后使用json\u populate\u record将每个值作为原始表类型，如下所示：

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 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"
;

关于冲突的条款仅适用于9.5或更高版本。在此之前，您必须对循环和重试或可写公共表表达式使用一些技巧（尽管这有竞争条件问题）；无论如何，如果您使用的是旧版本，那么这是升级的一个很好的理由。