Search code examples
jsonpostgresqlsql-updatejsonb

postgreSQL Adding New Key with value if it doesnt exist and edit it if it does

I'm trying to run the following but it's not updating columns with NULL values or columns that do not have this key: 

update A a set 
a.jsonbcolumn = ('{"key":' 1 '}')

I'm using postgres 9.6.3 and jsonb_set does not work for me. Any advice?

Thanks

Solution

  • jsonb_set() is the way to go:

    update a
  set jsonbcolumn = jsonb_set(jsonbcolumn, '{key}', '1');

    If it does not create the key, this is probably becase the column value is NULL rather than an empty JSON. In that case use coalesce():

    update a
  set jsonbcolumn = jsonb_set(coalesce(jsonbcolumn,'{}'), '{key}', '1');

    Online example: https://rextester.com/MSU66547