Postgres 9.6中如何遍历每条记录解析JSON列并插入新表？

作为PostgreSQL老玩家，我得先告诉你：尽量别用PL/pgSQL循环处理这种批量数据转换——PostgreSQL对集合操作的支持非常好，纯SQL写法不仅更简洁，性能也会甩循环几条街。先给你最优解：

推荐方案：纯SQL批量插入（无需循环） #

直接用INSERT ... SELECT结合json_array_elements来拆分JSON列表并插入目标表，这是处理这类场景的标准姿势：

INSERT INTO table2 (jsonelem1, jsonelem2, "DateOfAcquisition", "Id")
SELECT
    -- 这里根据你的JSON结构替换：如果jsonelem1对应JSON里的某个字段，就写成(i->>'jsonelem1_field')::INT
    NULL::INT, -- 示例：如果暂时没有jsonelem1的数据源，就填NULL，按需修改
    (i->>'jsonsubfield')::INT AS jsonelem2,
    t1."DateOfAcquisition",
    t1."Id"
FROM table1 t1,
     json_array_elements(t1."Myjson") AS i;

原理说明： #

这里用了PostgreSQL的横向连接（LATERAL JOIN的简写，9.3+版本支持），json_array_elements会把table1每条记录里的JSON列表拆成独立的行，然后一次性把所有拆分后的行插入table2，全程不需要写任何循环，效率拉满。

如果你一定要用PL/pgSQL循环（比如有复杂业务逻辑） #

如果你因为特殊需求必须用循环，那得先遍历table1的每条记录，再在每条记录里遍历JSON列表。你原来的代码有几个小问题，我帮你修正了：

DO $BODY$ 
DECLARE 
    rec record; -- 用来存储table1的每条记录
    i json; 
    myJsonelem1 INT; 
    myJsonelem2 INT; 
BEGIN 
    -- 第一步：遍历table1的所有记录
    FOR rec IN SELECT * FROM table1 LOOP
        -- 第二步：遍历当前记录的JSON列表
        FOR i IN SELECT * FROM json_array_elements(rec."Myjson") LOOP 
            -- 修正赋值语法：用:=，并且正确转换类型
            myJsonelem2 := (i->>'jsonsubfield')::INT; 
            -- 如果jsonelem1有对应JSON字段，在这里解析，比如：
            -- myJsonelem1 := (i->>'your_other_field')::INT;
            -- 插入时带上所有必填字段，包括原记录的Id
            INSERT INTO table2 (jsonelem1, jsonelem2, "DateOfAcquisition", "Id")
            VALUES (myJsonelem1, myJsonelem2, rec."DateOfAcquisition", rec."Id"); 
        END LOOP;
    END LOOP; 
END; 
$BODY$ LANGUAGE plpgsql;

你原来代码的问题点： #

1. 没有遍历table1的记录，直接把omgjson赋值成固定值，无法处理所有行
2. 变量赋值语法错误：myJsonelem2 i->> 'jsonsubfield'::INT; 应该用:=赋值，且类型转换的括号位置要正确
3. 插入时遗漏了Id字段，不符合table2的结构要求

最后再啰嗦一句：如果没有特殊的业务逻辑要处理，一定要用纯SQL的方案，循环在处理大量数据时性能会差很多，PostgreSQL的集合操作是专门优化过的。

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