Search code examples
postgresqljsonbpostgresql-12

Update whole JSONB object in Postgres 12

I am having a hard time to replace the JSONB object I have in a column named config with a fresh JSONB object.

According to Postgres (12) documentation, one should use jsonb_set in order to update existing JSON records: I am trying to replace the existing JSONB with the following instruction:

UPDATE table SET config=jsonb_set(config, '{config}', '{"newKey":"newValue"}') WHERE myUpdateCondition;

The problem is, the couple {"newKey":"newValue"} is successfully added to config but the previous JSONB still exists in config column: my goal is to totally update config with just {"newKey":"newValue"}, leaving nothing of the previous JSONB behind.

I was reading about jsonb_delete but I did not found it in the official documentation.

How could I update JSONB config column by leaving nothing of the previous JSONB object and replace it completely with a new JSONB object?

Solution

  • my goal is to totally update config with just {"newKey":"newValue"}, leaving nothing of the previous JSONB behind.

    Then just assign the new value:

    UPDATE table 
  SET config= '{"newKey":"newValue"}'
WHERE ...;