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
– RAJKUMAR PADILAM, Commented Feb 27, 2018 at 18:56

khoroshevj · Accepted Answer · 2020-08-20 17:36:11Z

3

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"}}

edited Aug 20, 2020 at 17:36

answered Feb 27, 2018 at 22:15

khoroshevj

1,13710 silver badges16 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related