Search code examples
postgresqljsonb

Add property to the top level of a jsonb object

From the documentation I can figure out how to replace or add a value at from an existing property

{"example":123}

UPDATE mytable
SET jsoncolumn = jsonb_set(jsoncolumn, '{example}', '"string123"')
WHERE id = 1;

{"example":"string123"}

However, I cannot figure out how to insert a property at the top level (beside a sibling)

{"example":123}

{"example":123,"property-to-add":"value"}

What is the easiest way to accomplish this (given that the actual object is pretty large)

Solution

  • You can append the new key/value pair:

    UPDATE mytable
  SET jsoncolumn = jsoncolumn || '{"property-to-add": "value"}'
WHERE id = 1;

    If property-to-add already exists, it will be replaced.