How to update an empty jsonb column using jsonb_set in postgresql?

Question

My purpose is to update a jsonb column using jsonb_set, which is currently null, with an object having more than one key-value pairs. The update command executes successfully but it is not updating anything, the column is still coming up empty. I am trying the following query.

UPDATE tab 
  set value = jsonb_set(value, '{}', '{"a" : 100, "b" : [100, 200]}'::jsonb) 
where id = 100;

Any solutions ?

if value is null initially then use like this coalesce(value,'[]'::jsonb). — Blu
– Blu, Commented Sep 19, 2023 at 15:18

Kaushik Nayak · Accepted Answer · 2019-02-01 07:24:04Z

5

From what I've understood, it appears you don't need jsonb_set for this case. Simply cast the string to jsonb for updating

UPDATE tab 
  set value = '{"a" : 100, "b" : [100, 200]}'::jsonb
where id = 100 
--and value is null; --additional check if you need.

Demo

answered Feb 1, 2019 at 7:24

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

2 Comments

JessePinkman Over a year ago

Yes, I know this way. But how to achieve the same if I want to do with jsonb_set only ? Or, what I'm asking is not possible at all ?

Kaushik Nayak Over a year ago

@ShaliniJ. : Most likely not possible and nothing that I know of using jsonb_set .jsonb_set works only on existing jsons, to set a particular value. You are actually adding a new json, so it should be either an insert or update.

Collectives™ on Stack Overflow

How to update an empty jsonb column using jsonb_set in postgresql?

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