Postgres 9.6 update jsonb column to add new attribute with a value from query

Question

I have a table xyz, with a metadata jsonb column in postgres.
Table : xyz column : metadata, type = jsonb

metadata = {"exceptions": {"first_exception": "first_value"} }

I want to add a new sub_attribute

desired metadata = {"exceptions": {"first_exception": "123"},{"second_exception": "234"}  }

I can use the

update xyz 
SET metadata = jsonb_set(metadata->'exceptions', '{second_exception}', '"234"', true).

But I want to get the value 234 from a select query. I am not able to figure how to combine the select query with the update to do this.

I add the structure. I just want to know how to use query's output as a value for the new attribute to be added — RAJKUMAR PADILAM

khoroshevj khoroshevj · Accepted Answer · 2018-02-27T22:15:13

You can do

UPDATE xyz
SET metadata = jsonb_set(metadata, '{exceptions, second_exception}', other.value::jsonb)
FROM other
WHERE other.column = xyz.column

Pay attention that {"exceptions": {"first_exception": "123"},{"second_exception": "234"}} is not a valid json and update will give you following result {"exceptions": {"first_exception": "123", "second_exception": "234"}}

Postgres 9.6 update jsonb column to add new attribute with a value from query

1 Answers