使用单个更新命令更新或创建嵌套的jsonb值

本文介绍了使用单个更新命令更新或创建嵌套的jsonb值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

让我们说我在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.