使用单个更新命令更新或创建嵌套的jsonb值 [英] Update or create nested jsonb value using single update command

问题描述

让我们说我在Postgres 9.6 中有一个名为xyz的JSONB列.在一个更新中，我想将此列的.foo.bar键设置为{"done":true}.

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

但是更新必须容忍xyz的更新前值是从{}到

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

{ 
    "abc": "Hello"
}

也许

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

所以我不能立即使用jsonb_set，因为如果未定义xyz->foo，它将失败.在那种情况下，我可以使用jsonb_insert，但是如果已经定义了xyz->foo ，那将失败.

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
)

...当未定义foo时也会失败，因为xyz->'foo'是null会在串联中覆盖{}.

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

很明显，我可以编写一个使用if的函数来解决此问题，但是我真的觉得我应该能够在单个更新中做到这一点.

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.

推荐答案

对于此示例:

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

插入:

您必须使用jsonb_insert，您可以使用SELECT对其进行测试.

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

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

注意:使用jsonb_insert对于正确设置路径非常重要.这里的路径为"{foo:bar}"，这意味着您将在名为bar的对象foo中插入JSON.

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.

因此，结果是:

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

设置:

要编辑bar并将其设置为false，必须使用jsonb_set.您可以使用SELECT进行测试:

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;

这将返回:

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

更新设置并插入

当对象存在时使用jsonb_set，当对象不存在时使用jsonb_insert.要在不知道要使用哪一个的情况下进行更新，可以使用CASE

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.

您可以添加一些CASE子句以获得更具体的值.

You can add some CASE clauses for more specific values.

这篇关于使用单个更新命令更新或创建嵌套的jsonb值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！