2

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 ?

JessePinkman
  • 613
  • 8
  • 15

1 Answers1

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

Kaushik Nayak
  • 30,772
  • 5
  • 32
  • 45
  • 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 ? – JessePinkman Feb 04 '19 at 07:53
  • @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. – Kaushik Nayak Feb 04 '19 at 08:05