Update jsonb column with value from row_to_json()

Question

I have a table containing a data that look like:

col1       col2     col3     col4      json_data  
----------------------------------------------------
 a           b        c       d       {"mock":"abc123"}
 e           f        g       h       {"mock":"def456"}

The column json_data is a column with jsonb type that contains some json that do not relate to anything which I want to update with row_to_json() function. The result should be something like this

col1       col2     col3     col4      json_data  
----------------------------------------------------
 a           b        c       d       {"col1:"a", "col2:"b","col3:"c","col4:"d"}
 e           f        g       h       {"col1:"e", "col2:"f","col3:"g","col4:"h"}

which will get the result from row_to_json function to update each row. I am not sure how can I use the UPDATE query to do this.

klin · Accepted Answer · 2019-05-22 14:27:57Z

2

Use the function to_jsonb() and the - operator to remove the column json_data from the resulting json object:

create table my_table(col1 text, col2 text, col3 text, col4 text, json_data jsonb);
insert into my_table values
('a', 'b', 'c', 'd', '{"mock":"abc123"}'),
('e', 'f', 'g', 'h', '{"mock":"def456"}');

update my_table t
set json_data = to_jsonb(t)- 'json_data'
returning *;

 col1 | col2 | col3 | col4 |                      json_data                       
------+------+------+------+------------------------------------------------------
 a    | b    | c    | d    | {"col1": "a", "col2": "b", "col3": "c", "col4": "d"}
 e    | f    | g    | h    | {"col1": "e", "col2": "f", "col3": "g", "col4": "h"}
(2 rows)

You can remove more than one column, e.g.:

update my_table t
set json_data = to_jsonb(t)- 'json_data'- 'col3'- 'col4'
returning *;

 col1 | col2 | col3 | col4 |         json_data          
------+------+------+------+----------------------------
 a    | b    | c    | d    | {"col1": "a", "col2": "b"}
 e    | f    | g    | h    | {"col1": "e", "col2": "f"}
(2 rows)

Alternatively, you can use jsonb_build_object() instead of to_jsonb():

update my_table t
set json_data = jsonb_build_object('col1', col1, 'col2', col2)
returning *;

 col1 | col2 | col3 | col4 |         json_data          
------+------+------+------+----------------------------
 a    | b    | c    | d    | {"col1": "a", "col2": "b"}
 e    | f    | g    | h    | {"col1": "e", "col2": "f"}
(2 rows)

edited May 22, 2019 at 14:27

answered May 22, 2019 at 14:07

klin

123k15 gold badges240 silver badges262 bronze badges

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

9 Comments

emp Over a year ago

Sorry for not being specific but if I want just only a specific column to be in the json_data e.g. col1 and col2 in json_data. How can I do that?

klin Over a year ago

Remove unwanted columns just like json_data was removed.

emp Over a year ago

Can you give some example? I am not really sure about that (currently not at my desk rn)

emp Over a year ago

Sorry but can this be done with an ordered attribute like col1, col2 ,col3 and so on. I tried both to_jsonb and jsonb_build_object but not worked e.g. it showed as col2, col1, col3. I need it to be in the same order as the column

emp Over a year ago

I knew that json is an unordered collection of value pairs but can it be fixed?

|

Collectives™ on Stack Overflow

Update jsonb column with value from row_to_json()

1 Answer 1

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related