PostgreSQL JSON array value from another column-CodePudding

I have employee table in postgreSQL

CREATE TABLE Employee(EmployeeID integer PRIMARY KEY AUTO_INCREMENT,
Employeename VARCHAR(100));

alter table Employee add column parents JSON;

Now, I want to update JSON column with JSON array with a value from the existing column like below.

update employee set parents = json_array_elements('[{"name":Employeename, "id":EmployeeID }]')

Any way I can achieve this?

CodePudding user response：

Try using:

JSON_BUILD_OBJECT function, to generate your json element
JSON_BUILD_ARRAY function, to enclose your json object into an array

UPDATE employee 
SET parents = JSON_BUILD_ARRAY(
                  JSON_BUILD_OBJECT('name', Employeename, 
                                    'id'  , EmployeeID   ));

Check the demo here.

If you need to store it as an array, since