使用单个Update命令更新或创建嵌套JSONB值

Question

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

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

{ 
    "abc": "Hello"
}

或也许

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

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

所以我尝试使用串联，

之类的东西

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

...当foo不确定时也会失败，因为xyz->'foo'是null，它覆盖了{}。

显然我可以编写一个使用if来解决此问题的函数，但我真的觉得我应该能够在一次更新中进行。

Answer 1

在此示例中：

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

插入：

您必须使用jsonb_insert您可以使用SELECT进行测试。

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

注意：使用jsonb_insert正确设置路径确实很重要。这里的路径是'{foo：bar}'，这意味着您将在对象foo中插入一个称为 bar的对象。

因此，结果是：

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

set：

要编辑bar并将其设置为False，您必须使用jsonb_set。您可以使用SELECT：

对其进行测试

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

此返回：

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

set and Insert

更新

当对象存在时，您使用jsonb_set，而jsonb_insert则使用。要更新而不知道要使用哪一个，您可以使用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.

您可以为更具体的值添加一些案例子句。

Answer 2

您可以只使用||加入。它将覆盖或添加任何JSON值。

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

很容易。我很少在软件中使用这些功能。

在合并的情况下：

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