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.

Dan · Accepted Answer · 2018-02-20 18:35:13Z

43

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.

edited Feb 20, 2018 at 18:35

answered Feb 20, 2018 at 16:37

Dan

1,9211 gold badge16 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dan Over a year ago

Updated my answer, the last part has the answer you need. If I got your question right

Dan Over a year ago

The UPDATE FOR SET AND INSERT works with { }, { "abc":"hello" }, { "foo": { "baz": { "done": false } }, "abc": "Hello" }. And if there is bar { "foo": { "bar": { "done": true} }} it sets done to false.

Jesper We Over a year ago

Yes, that does work for the specific example I gave, so I give you 10 points ;-). Cumbersome to generalize, though, as the nesting goes deeper. For now I have resorted to doing it in Javascript...

eatmeimadanish · Accepted Answer · 2020-08-31 04:45:13Z

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

edited Aug 31, 2020 at 4:45

answered Feb 20, 2018 at 16:20

eatmeimadanish

3,9231 gold badge16 silver badges21 bronze badges

2 Comments

Jesper We Over a year ago

I am afraid not. For the case where foo.baz exists, just using concatenation will loose it: select '{"foo":{"baz":123}}'::jsonb || '{"foo":{"bar":{"done":true}}}'::jsonb yields only {"foo":{"bar":{"done":true}}} instead of the desired {"foo":{"baz":123, "bar":{"done":true}}}

eatmeimadanish Over a year ago

I updated to include a merge function. The first item is the original json, the second is the one you want to add.

Collectives™ on Stack Overflow

Update or create nested jsonb value using single update command

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related