PostgreSQL JSON array value from another column

Question

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?

lemon · Accepted Answer · 2023-01-24 17:45:33Z

1

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

edited Jan 24, 2023 at 17:45

answered Jan 24, 2023 at 17:34

lemon

15.6k6 gold badges23 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

rakeeee Over a year ago

I need this as JSON array not object

lemon Over a year ago

What's the purpose of having an array when the object is only one? What are you aggregating on?

rakeeee Over a year ago

I will get updates in time to add several other objects

lemon Over a year ago

Nesting json_build_object into json_build_array should do it. Try the demo now.

Hun · Accepted Answer · 2023-11-14 11:30:13Z

0

if parents is json[]

UPDATE employee 
SET parents = array[
                  JSON_BUILD_OBJECT('name', Employeename, 
                                    'id'  , EmployeeID   )]::json[];

https://stackoverflow.com/a/35081935/1025379

answered Nov 14, 2023 at 11:30

Hun

3,80641 silver badges75 bronze badges

Collectives™ on Stack Overflow

PostgreSQL JSON array value from another column

2 Answers 2

4 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Linked

Related