PostgreSQL脚本中如何为JSONB列添加数组属性并更新记录？

问题原因 #

你原代码里直接用mt.column.arr_prop=arr修改JSONB字段的方式是错误的——PostgreSQL的PL/pgSQL不支持通过点语法直接修改JSONB内部的属性，必须用JSONB专用操作函数调整其结构。另外，逐行循环更新的效率极低，优先推荐批量更新的方式。

方案一：批量更新（高效推荐） #

用单条UPDATE语句结合jsonb_set和jsonb_agg完成批量更新，无需循环：

UPDATE table1 t1
SET "column" = jsonb_set(
    t1."column",
    '{arr_prop}', -- 指定要添加的JSON路径
    (SELECT jsonb_agg(col1) FROM table2 t2 WHERE t2.id_ext = t1.id), -- 将table2的col1转为JSON数组
    true -- 允许添加不存在的键
)
-- 可选：仅更新table2中有对应数据的行，避免无数据行的arr_prop被设为null
WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE t2.id_ext = t1.id);

如果需要给无对应数据的行也添加arr_prop为空数组，可去掉WHERE EXISTS子句，将子查询改为COALESCE((SELECT jsonb_agg(col1) FROM table2 t2 WHERE t2.id_ext = t1.id), '[]'::jsonb)。

方案二：修改原循环代码 #

如果必须保留循环逻辑，需要先将TEXT数组转为JSONB类型，再用jsonb_set修改字段：

DO $$ 
DECLARE
  mt table1%ROWTYPE;
  arr_json jsonb;
BEGIN
    FOR mt IN 
        SELECT * FROM table1
    LOOP
        -- 将table2查询结果转为JSONB数组
        arr_json := (SELECT jsonb_agg(col1) FROM table2 WHERE id_ext=mt.id);
        
        raise notice 'arr_json=%', arr_json;

        -- 使用jsonb_set添加/修改arr_prop属性
        mt."column" := jsonb_set(mt."column", '{arr_prop}', arr_json, true);

        UPDATE table1 SET "column"=mt."column" WHERE id=mt.id;

    END LOOP;
END $$;

注意：column是PostgreSQL的关键字，建议用双引号括起来避免语法冲突。

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