How to append a custom object to JSONB column type in postgres

Question

I need to update a jsonb column which contains data {"X":true}. I need to append a complex object of the type {"obj":{"a":1,"b":2}} so the final value of row for that column {"x":true,"obj":{"a":1,"b":2}} . What will be the query to update this row .

postgres version 12

Update - The following query update tableName set columnName = (select '{"obj":{"a":1,"b":2}}'::jsonb || columnName::jsonb) where ... returns successfully when there is a value present , but when the column is null it still remains null after running the update query . I need to be able to add {"obj":{"a":1,"b":2}} even when the column is null .

score 1 · Accepted Answer · 2021-02-26 16:40:12Z

1

You can use the concatenation operator:

'{"X":true}'::jsonb || '{"obj":{"a":1,"b":2}}'::jsonb

If you want to update an existing column, use coalesce() to deal with NULL values:

update the_table
  set the_column = coalesce(the_column, '{}')||'{"obj":{"a":1,"b":2}}'

Online example

edited Feb 26, 2021 at 16:40

answered Feb 26, 2021 at 14:16

user330315

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

2 Comments

darth_vader Over a year ago

The following query update tableName set columnName = (select '{"obj":{"a":1,"b":2}}'::jsonb || columnName::jsonb) where ... returns successfully when there is a value present , but when the column is null it still remains null after running the update query .

user330315 Over a year ago

@divyamsingh13: use coalesce() - see my edit. The select is useless btw.

Collectives™ on Stack Overflow

How to append a custom object to JSONB column type in postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related