PostgreSQL如何利用现有列值更新JSON数组类型列？

首先，你当前的UPDATE语句存在两个核心问题：

• json_array_elements是解析JSON数组的函数，作用是将数组拆分成多行元素，并非用于构造JSON数组。
• 直接在字符串'[{"name":Employeename, "id":EmployeeID }]'中写列名，PostgreSQL会把Employeename和EmployeeID当成普通字符串字面量，不会引用表中的实际列值。

要实现将每行的EmployeeID和Employeename封装成JSON数组并更新到parents列，你可以使用PostgreSQL原生的JSON构造函数：

正确的UPDATE语句 #

UPDATE employee
SET parents = json_build_array(
    json_build_object('name', Employeename, 'id', EmployeeID)
);

语句解释： #

• json_build_object('name', Employeename, 'id', EmployeeID)：将当前行的Employeename和EmployeeID列值，构造成格式为{"name": "xxx", "id": 123}的JSON对象。
• json_build_array(...)：把生成的JSON对象包裹为一个JSON数组，最终得到[{"name": "xxx", "id": 123}]格式的值，写入parents列。

可选优化：使用JSONB类型（推荐） #

如果业务场景允许，建议将parents列改为JSONB类型（比JSON更高效，支持索引和更多JSON操作），对应的构造函数为jsonb_build_array和jsonb_build_object：

-- 先修改列类型
ALTER TABLE employee ALTER COLUMN parents TYPE JSONB USING parents::JSONB;

-- 执行更新
UPDATE employee
SET parents = jsonb_build_array(
    jsonb_build_object('name', Employeename, 'id', EmployeeID)
);

验证结果 #

更新完成后，可通过以下查询确认效果：

SELECT EmployeeID, Employeename, parents FROM employee;

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