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

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? Thanks.

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 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

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

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

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

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

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.

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;

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