PostgreSQL解析JSON列表并实现循环增改操作的方法

针对你的需求，我们可以结合PostgreSQL的JSON处理能力和INSERT ... ON CONFLICT语法来实现批量的更新/插入逻辑。下面是完整的实现方案：

前提准备 #

首先要确保你的notification表中，notificationid列已经设置了主键约束或者唯一约束，这样ON CONFLICT才能识别重复的记录：

ALTER TABLE notification ADD CONSTRAINT pk_notification_id PRIMARY KEY (notificationid);

存储过程实现 #

CREATE OR REPLACE PROCEDURE process_notification_list(p_json_data jsonb)
LANGUAGE plpgsql
AS $$
BEGIN
  -- 遍历JSON数组，提取每条通知数据并执行Upsert
  INSERT INTO notification (notificationid, userid)
  SELECT
    (elem->>'notificationId')::integer AS notificationid,
    (elem->>'userId')::integer AS userid
  FROM jsonb_array_elements(p_json_data->'list') AS elem
  ON CONFLICT (notificationid) DO UPDATE
    SET userid = EXCLUDED.userid; -- 如果记录存在，更新userId字段
END;
$$;

代码说明 #

• jsonb_array_elements(p_json_data->'list')：把传入的JSON中的list数组拆分成单行的JSON对象，每条对象对应一条通知数据。
• elem->>'notificationId'：用->>运算符提取JSON对象中的字符串值，再通过::integer转换为整数类型（适配表中的字段类型）。
• ON CONFLICT (notificationid) DO UPDATE：当插入的notificationid已经存在时，执行更新操作，这里我们更新userid为新传入的值。

调用存储过程 #

你可以这样调用这个存储过程，传入你的JSON数据：

CALL process_notification_list('{"list": [{"notificationId": 123, "userId": 444}, {"notificationId": 456, "userId": 789}]}'::jsonb);

如果你的JSON数据是json类型而不是jsonb，只需要把参数类型改成json，并将jsonb_array_elements换成json_array_elements即可。

内容的提问来源于stack exchange，提问作者vinod hy