时间戳

首页 > 解决方案 > 如何更新 jsonb 的 PostgreSQL 数组

postgresql - 如何更新 jsonb 的 PostgreSQL 数组

问题描述

我有一张像这样的桌子：

id: integer,
... other stuff...,
comments: array of jsonb

其中评论列具有以下结构：

[{
        "uid": "comment_1",
        "message": "level 1 - comment 1",
        "comments": [{
            "uid": "subcomment_1",
            "message": "level 2 - comment 1",
            "comments": []
        }, {
            "uid": "subcomment_2",
            "message": "level 1 - comment 2",
            "comments": []
        }]
    },
    {
        "uid": "P7D1hbRq4",
        "message": "level 1 - comment 2",
        "comments": []
    }
]

我需要更新一个特定的字段，例如：comments[1](with uid = comment_1) -> comments[2] (with uid = subcomment_2) -> message = 'comment edited'.

我是 postgresql 的新手，我无法弄清楚如何做到这一点，甚至没有关闭。我设法合并对象并更改级别 1 的消息：

UPDATE tasks
    set comments[1] = comments[1]::jsonb || $$
      {
        "message": "something",
      }$$::jsonb
where id = 20;

但这是我能做到的。

对正确方向的任何提示？

LE：我做到了这一点：

UPDATE tasks
set comments[1] = jsonb_set(comments[1], '{comments,1, message}', '"test_new"')
where id = 20;

当然，我可以从 javascript 获得这条路径，但这是最佳实践吗？使用 javascript 数组中的索引感觉不舒服。我应该尝试编写一个 sql 函数来获取数组并使用“uid”作为键吗？使用 'uid' 搜索/选择的任何其他更简单的方法？

LLE

我无法使用以下建议使其工作：这个问题（我阅读并尝试过）下面的代码没有返回任何内容：

-- get index for level 2
select pos as elem_index
from tasks,
     jsonb_array_elements(comments[0]->'comments') with ordinality arr(elem, pos)
where tasks.id = 20 and
      elem ->>'uid'='subcomment_1';

我需要它的几个级别，所以它不是完全重复的。

标签： postgresqljsonbpostgresql-11

解决方案

首先，您不能更新列的一部分（数组的元素），而只能更新整个列。

接下来，您应该了解路径（jsonb_set()函数的第二个参数）的含义。

最后，函数的第三个参数是一个有效的 json，所以一个简单的文本值必须用单引号和双引号括起来。

update tasks
set comments = jsonb_set(comments, '{0, comments, 1, message}', '"comment edited"')
where id = 1;

小路：

  • 0 - 外部数组的第一个元素（元素从 0 开始索引）
  • 评论- 带有键的对象comments
  • 1comments -数组的第二个元素
  • messagemessage -上述元素中的一个对象。

参见Db<>fiddle。

推荐阅读