Update or create nested jsonb value using single update command

Question

Let us say I have [in Postgres 9.6] a JSONB column named xyz. In an update, I want to set the .foo.bar key of this column to {"done":true}.

But the update must tolerate that the pre-update value for xyz is anything from {} to

{ 
    "abc": "Hello"
}

or maybe

{ 
    "foo": {
        "baz": { "done": false }
    },
    "abc": "Hello"
}

So I cannot use jsonb_set straight away, because it fails if xyz->foo is undefined. In that case I could use jsonb_insert, but that fails if xyz->foo is already defined.

So I try to use concatenation, with something like

jsonb_set( 
    jsonb_set(xyz, '{foo}', '{}'::jsonb || xyz->'foo', true),
    '{foo, bar}', '{"done":true}', true
)

...which also fails when foo is undefined since xyz->'foo' is null which overrides {} in the concatenation.

Obviously I could write a function that uses an if to get around this, but I really feel I should be able to do it in a single update.

Answer 1

For this example:

{ 
    "foo": {
        "baz": { "done": false }
    },
    "abc": "Hello"
}

INSERT:

You have to use jsonb_insert you can test it with a SELECT.

SELECT jsonb_insert(xyz, '{foo,bar}', '{"done":true}'::jsonb) FROM tablename;

Note: With jsonb_insert is really important to set the path correctly. Here the path is '{foo:bar}' meaning that you will insert a JSON inside the object foo called bar.

Hence, the result is:

{
    "abc": "Hello",
    "foo": {
        "baz": {
            "done": false
        },
        "bar": {
            "done": true
        }
    }
}

SET:

To edit bar and set it to false you have to use jsonb_set. You can test it with SELECT:

SELECT jsonb_set(xyz, '{foo,bar}', '{"done":false}'::jsonb) FROM tablename;

This returns:

{
    "abc": "Hello",
    "foo": {
        "baz": {
            "done": false
        },
        "bar": {
            "done": false
        }
    }
}

UPDATE FOR SET AND INSERT

You use jsonb_set when the object exists and jsonb_insert when it doesn't. To update without knowing which one to use, you can use CASE

UPDATE tablename SET 
xyz= (CASE
        WHEN xyz->'foo' IS NOT NULL
        THEN jsonb_set(xyz, '{foo,bar}', '{"done":false}'::jsonb)
        WHEN xyz->'foo' IS NULL
        THEN jsonb_insert(xyz, '{foo}', '{"bar":{"done":true}}'::jsonb)
    END)
WHERE id=1;-- if you use an id to identify the JSON.

You can add some CASE clauses for more specific values.

Answer 2

You can just use || to concatenate. It will overwrite or add any json value.

SELECT '{}'::jsonb || '{"foo":"bar"}'::jsonb
UPDATE tablename SET jdoc = jdoc || '{"foo":"bar"}'::jsonb

It's that easy. I rarely use the functions in my software.

In the case of merging:

create or replace function jsonb_merge(orig jsonb, delta jsonb)
returns jsonb language sql as $$
    select
        jsonb_object_agg(
            coalesce(keyOrig, keyDelta),
            case
                when valOrig isnull then valDelta
                when valDelta isnull then valOrig
                when (jsonb_typeof(valOrig) <> 'object' or jsonb_typeof(valDelta) <> 'object') then valDelta
                else jsonb_merge(valOrig, valDelta)
            end
        )
    from jsonb_each(orig) e1(keyOrig, valOrig)
    full join jsonb_each(delta) e2(keyDelta, valDelta) on keyOrig = keyDelta
$$;