Home > Mobile >  PostgreSQL JSON array value from another column
PostgreSQL JSON array value from another column

Time:01-25

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

  • Related