How to perform update operations on columns of type JSONB

Question

Looking through the documentation for the Postgres 9.4 datatype JSONB, it is not immediately obvious to me how to do updates on JSONB columns.

Documentation for JSONB types and functions:

http://www.postgresql.org/docs/9.4/static/functions-json.html http://www.postgresql.org/docs/9.4/static/datatype-json.html

As an examples, I have this basic table structure:

CREATE TABLE test(id serial, data jsonb);

Inserting is easy, as in:

INSERT INTO test(data) values ('{"name": "my-name", "tags": ["tag1", "tag2"]}');

Now, how would I update the 'data' column? This is invalid syntax:

UPDATE test SET data->'name' = 'my-other-name' WHERE id = 1;

Is this documented somewhere obvious that I missed?

Krimmer

Answer 1

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

Preclude

This answer only concerns with the type JSON and ignores JSONB. – Asare

@fiatjaf: This answer is fully applicable to the data types json and jsonb alike. Both store JSON data, jsonb does it in a normalized binary form that has some advantages (and few disadvantages). https://mcmap.net/q/109154/-how-do-i-query-using-fields-inside-the-new-postgresql-json-datatype Neither data type is good for being manipulating a lot inside the database. No document type is. Well, it's fine for small, hardly structured JSON documents. But big, nested documents would be a folly that way. – Preclude

"Instructions how to work with the tools of Postgres 9.3" really aught to be first in your answer as it answers the question asked.. sometimes it makes sense to update json for maintenance / schema changes etc and the reasons not to do update json don't really apply – Thymelaeaceous

I don't think it is correct that PostgreSQL always rewrites the whole row when an update is performed. This is only the case for rows that fit within a single physical page (normally 8kb). Rows that are larger are split internally and as far as I understand only the page(s) affected by the update will be rewritten. postgresql.org/docs/9.6/static/storage.html – Merow

Not really sure if it's related to the downvotes (I didn't downvote), but the issue might be more because it's a "you shouldn't be doing that" answer rather than a "people don't seem to like the idea: a normalized..." problem. I think you're right, storing a lot in JSON/B probably indicates a larger problem and is far from the ideal, but a lot of us have to work with pre-existing systems and need to learn how to work with what we have until we're able to improve those systems. – Lepine

Answer the question first before adding your own comment/opinion/discussion. – Foch

This answer is largely irrelevant with current generation of postgresql (versions 11, 12). There is much better support of Jsonb operations and there are many reasons to switch to that as being mainstream type for many applications. – Priest

@taleodor: JSON support has been improved with every version and is pretty excellent by now. Has been for some time. And very useful for certain applications. But my answer is still fully applicable - especially for "update operations" this question asks about - as it addresses a principle limitation of document types. For regular data, proper columns in a more or less normalized db schema are typically much more efficient. That is not going to change. The Postgres project advises accordingly, like I quoted above - unaltered up to Postgres 13 devel manual. – Preclude

In addition to @MichaelWasser comment - the concept of normalization is irrelevant to the OP's question. Normalization is entirely out of scope and brings its own pros, cons and justifications. – Chetnik

People should know first why doing things that way. Love the philosophy. – Relive

Be very careful when reading this answer. For each blog post listed in this answer, there are equal and opposite views on this. – Alamode

Answer 2

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.

Zingaro

you get bonus points for showing how to update a column in a table as the OP requested – Pleven

I know this was not in the original request, but I'd love to see an example of how to chain these, e.g. remove two keys and set two keys in one UPDATE. – Pleven

@chadrik: I added a more complex example. It doesn't do exactly what you requested, but it should give you an idea. Note that the input to the outer jsonb_set call is the output from the inner call, and that the input to that inner call is the result of data #- '{tags,-1}'. I.e., the original data with the last tag removed. – Zingaro

i dont kow tag index. how to delete the tag by value ? – Farnesol

@PranaySoni: For that purpose, I'd probably use a stored procedure or, if the overhead isn't a concern, bring that data back, manipulate it in the application's language, then write it back. This sounds heavy, but keep in mind, in all the examples I gave, you're not still not updating a single field in the JSON(B): you're overwriting the whole column either way. So a stored proc is really no different. – Zingaro

@Zingaro is "'{tags,999999999}'" an array append hack? How does it work? – Kraemer

@Alex: Yes, a bit of a hack. If I said {tags,0}, that would mean "the first element of array tags", allowing me to give a new value to that element. By using a large number instead of 0, instead of it replacing an existing element in the array, it adds a new element to the array. However, if the array actually had more than 999,999,999 elements in it, this would replace the last element instead of add a new one. – Zingaro

what about if field contains null? looks dont' work. Eg info jsonb field is null: "UPDATE organizer SET info = jsonb_set(info, '{country}', '"FRA"') where info->>'country'::text IS NULL; " I get UPDATE 105 record but no changes on db – Outandout

What if the jsonb contains a sub json? Something like "nested_tag":{"subtag1":true, "subtag2":"value"}. i'm trying to modify/add a subtag. I'm trying Set data = jsonb_set(data, '{nested_tag}->{subtag3}', true). But I get ERROR: function jsonb_set(jsonb, unknown, boolean) does not exist. Any idea? – Quinn

Does anyone think updating single keys in JSONb column is not concurrency safe, not from data base perspective but from Application data integrity view? – Poole

Answer 3

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

Svensen

Another issue in this line, is the use of jsonb_build_object(), because x->key, not returns key-object pair, to populate you need jsonb_set(target, path, jsonb_build_object('key',x->key)). – Billie

Answer 4

For those that run into this issue and want a very quick fix (and are stuck on 9.4.5 or earlier), here is a potential solution:

Creation of test table

CREATE TABLE test(id serial, data jsonb);
INSERT INTO test(data) values ('{"name": "my-name", "tags": ["tag1", "tag2"]}');

Update statement to change jsonb value

UPDATE test 
SET data = replace(data::TEXT,': "my-name"',': "my-other-name"')::jsonb 
WHERE id = 1;

Ultimately, the accepted answer is correct in that you cannot modify an individual piece of a jsonb object (in 9.4.5 or earlier); however, you can cast the jsonb column to a string (::TEXT) and then manipulate the string and cast back to the jsonb form (::jsonb).

There are two important caveats

1. this will replace all values equaling "my-name" in the json (in the case you have multiple objects with the same value)
2. this is not as efficient as jsonb_set would be if you are using 9.5

Hanus

Good lord, I have been searching for how to do an update to jsonb for like two hours so I could replace all \u0000 null characters, example showed the complete picture. Thanks for this! – Perse

looks good! btw the second argument to replace in your example includes the colon and the third does not. Looks like your call should be replace(data::TEXT, '"name":', '"my-other-name":')::jsonb – Bandit

Thank you @davidicus! Sorry for the very delayed update, but I appreciate you sharing for others! – Hanus

If you go this route just be very careful to sanitize your user input so that they can't pollute your data. – Redeploy

Answer 5

update the 'name' attribute:

UPDATE test SET data=data||'{"name":"my-other-name"}' WHERE id = 1;

and if you wanted to remove for example the 'name' and 'tags' attributes:

UPDATE test SET data=data-'{"name","tags"}'::text[] WHERE id = 1;

Merow

In addition, to update a JSON attribute with a dynamic value provided that escaping is not required, this can be used: update test set data = data || format('{"name": "%s"}', other_column)::jsonb – Disassemble

Answer 6

This question was asked in the context of postgres 9.4, however new viewers coming to this question should be aware that in postgres 9.5, sub-document Create/Update/Delete operations on JSONB fields are natively supported by the database, without the need for extension functions.

See: JSONB modifying operators and functions

Obsequent

Answer 7

I wrote small function for myself that works recursively in Postgres 9.4. I had same problem (good they did solve some of this headache in Postgres 9.5). Anyway here is the function (I hope it works well for you):

CREATE OR REPLACE FUNCTION jsonb_update(val1 JSONB,val2 JSONB)
RETURNS JSONB AS $$
DECLARE
    result JSONB;
    v RECORD;
BEGIN
    IF jsonb_typeof(val2) = 'null'
    THEN 
        RETURN val1;
    END IF;

    result = val1;

    FOR v IN SELECT key, value FROM jsonb_each(val2) LOOP

        IF jsonb_typeof(val2->v.key) = 'object'
            THEN
                result = result || jsonb_build_object(v.key, jsonb_update(val1->v.key, val2->v.key));
            ELSE
                result = result || jsonb_build_object(v.key, v.value);
        END IF;
    END LOOP;

    RETURN result;
END;
$$ LANGUAGE plpgsql;

Here is sample use:

select jsonb_update('{"a":{"b":{"c":{"d":5,"dd":6},"cc":1}},"aaa":5}'::jsonb, '{"a":{"b":{"c":{"d":15}}},"aa":9}'::jsonb);
                            jsonb_update                             
---------------------------------------------------------------------
 {"a": {"b": {"c": {"d": 15, "dd": 6}, "cc": 1}}, "aa": 9, "aaa": 5}
(1 row)

As you can see it analyze deep down and update/add values where needed.

Eximious

This doesn't work in 9.4, because jsonb_build_object was introduced in 9.5 – Destruction

@Destruction You are right, I just checked and I'm running PostgreSQL 9.5 now - this is why it works. Thanks for pointing that out - my solution will not work in 9.4. – Eximious

@J.Raczkiewicz Function works great! How do I enhance your function to add an insert if value doesn't exist? This is needed in case of null column value (e.g. empty column that doesn't yet have a {}) Similar to the create if missing boolean in jsonb_set function. jsonb_set ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean ] ) postgresql.org/docs/13/functions-json.html. – Intracranial

Answer 8

The top answer to this question is a bit dated.

Modern postgres has a much more elegant syntax:

UPDATE test SET data['name'] = 'my-other-name' where id = 1;

Current docs: https://www.postgresql.org/docs/current/datatype-json.html#JSON-KEYS-ELEMENTS

Miskolc

Answer 9

Maybe: UPDATE test SET data = '"my-other-name"'::json WHERE id = 1;

It worked with my case, where data is a json type

Ellon

Worked for me too, on postgresql 9.4.5. The whole record is rewritten so one can't update a single field atm. – Adoptive

Answer 10

Matheus de Oliveira created handy functions for JSON CRUD operations in postgresql. They can be imported using the \i directive. Notice the jsonb fork of the functions if jsonb if your data type.

9.3 json https://gist.github.com/matheusoliveira/9488951

9.4 jsonb https://gist.github.com/inindev/2219dff96851928c2282

Killion

Answer 11

Updating the whole column worked for me:

UPDATE test SET data='{"name": "my-other-name", "tags": ["tag1", "tag2"]}' where id=1;

Flyboat