让我们说我在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值
发布评论