How to perform update operations on columns of type JSONB in Postgres 9.4

Ideally, you don't use JSON documents for structured, regular data that you want to manipulate inside a relational database. Use a normalized relational design instead.

JSON is primarily intended to store whole documents that do not need to be manipulated inside the RDBMS. Related:

• JSONB with indexing vs. hstore

Updating a row in Postgres always writes a new version of the whole row. That's the basic principle of Postgres' MVCC model. From a performance perspective, it hardly matters whether you change a single piece of data inside a JSON object or all of it: a new version of the row has to be written.

Thus the advice in the manual:

JSON data is subject to the same concurrency-control considerations as any other data type when stored in a table. Although storing large documents is practicable, keep in mind that any update acquires a row-level lock on the whole row. Consider limiting JSON documents to a manageable size in order to decrease lock contention among updating transactions. Ideally, JSON documents should each represent an atomic datum that business rules dictate cannot reasonably be further subdivided into smaller datums that could be modified independently.

The gist of it: to modify anything inside a JSON object, you have to assign a modified object to the column. Postgres supplies limited means to build and manipulate json data in addition to its storage capabilities. The arsenal of tools has grown substantially with every new release since version 9.2. But the principal remains: You always have to assign a complete modified object to the column and Postgres always writes a new row version for any update.

Some techniques how to work with the tools of Postgres 9.3 or later:

• How do I modify fields inside the new PostgreSQL JSON datatype?

This answer has attracted about as many downvotes as all my other answers on SO together. People don't seem to like the idea: a normalized design is superior for regular data. This excellent blog post by Craig Ringer explains in more detail:

• "PostgreSQL anti-patterns: Unnecessary json/hstore dynamic columns"

Another blog post by Laurenz Albe, another official Postgres contributor like Craig and myself:

• JSON in PostgreSQL: how to use it right

If you're able to upgrade to Postgresql 9.5, the jsonb_set command is available, as others have mentioned.

In each of the following SQL statements, I've omitted the where clause for brevity; obviously, you'd want to add that back.

Update name:

UPDATE test SET data = jsonb_set(data, '{name}', '"my-other-name"');

Replace the tags (as oppose to adding or removing tags):

UPDATE test SET data = jsonb_set(data, '{tags}', '["tag3", "tag4"]');

Replacing the second tag (0-indexed):

UPDATE test SET data = jsonb_set(data, '{tags,1}', '"tag5"');

Append a tag (this will work as long as there are fewer than 999 tags; changing argument 999 to 1000 or above generates an error. This no longer appears to be the case in Postgres 9.5.3; a much larger index can be used):

UPDATE test SET data = jsonb_set(data, '{tags,999999999}', '"tag6"', true);

Remove the last tag:

UPDATE test SET data = data #- '{tags,-1}'

Complex update (delete the last tag, insert a new tag, and change the name):

UPDATE test SET data = jsonb_set(
    jsonb_set(data #- '{tags,-1}', '{tags,999999999}', '"tag3"', true), 
    '{name}', '"my-other-name"');

It's important to note that in each of these examples, you're not actually updating a single field of the JSON data. Instead, you're creating a temporary, modified version of the data, and assigning that modified version back to the column. In practice, the result should be the same, but keeping this in mind should make complex updates, like the last example, more understandable.

In the complex example, there are three transformations and three temporary versions: First, the last tag is removed. Then, that version is transformed by adding a new tag. Next, the second version is transformed by changing the name field. The value in the data column is replaced with the final version.

This is coming in 9.5 in the form of jsonb_set by Andrew Dunstan based on an existing extension jsonbx that does work with 9.4