PostgreSQL - 更新 JSONB 数组的语法

Question

我在尝试寻找合适的语法，用于更新Postgres 9.6.6中的jsonb列中的数组。

假设有一个名为“comments”的列，例如：

[
  {
    "Comment": "A",
    "LastModified": "1527579949"
  },
  {
    "Comment": "B",
    "LastModified": "1528579949"
  },
  {
    "Comment": "C",
    "LastModified": "1529579949"
  }
]

如果我想在每个评论后面添加Z（即AZ，BZ，CZ），我该怎么做？

我知道我需要使用类似于jsonb_set(comments, '{"Comment"}',这样的东西。

有什么提示可以帮助我完成这个任务吗？

谢谢。

- Martin Flower

Answer 1

尝试：

UPDATE elbat
       SET comments = array_to_json(ARRAY(SELECT jsonb_set(x.original_comment,
                                                           '{Comment}',
                                                           concat('"',
                                                                  x.original_comment->>'Comment',
                                                                  'Z"')::jsonb)
                                                 FROM (SELECT jsonb_array_elements(elbat.comments) original_comment) x))::jsonb;

它使用jsonb_array_elements()将数组元素作为集合获取，使用jsonb_set()对其进行更改，使用array_to_json()将其转换为数组，然后再转换为json。

但这是非常繁琐的工作。好吧，也许有一种更优雅的解决方案，但我没有找到。但由于您的JSON似乎已经有一个固定的模式，所以我建议重新设计，以关系方式进行，并为评论设置一个简单的表以及用于对象的链接表。在这样的模型中，更改肯定会非常容易。

- sticky bit

---

我同意关系型数据库更有意义。这是一个现有的数据库，也是我第一次接触Postgres的jsonb数据类型。 - Martin Flower

Answer 2

找到一个返回预期结果的查询：

select jsonb_agg(value || jsonb_build_object('Comment', value->>'Comment' || 'Z'))
from my_table
cross join jsonb_array_elements(comments);

                                                                      jsonb_agg                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 [{"Comment": "AZ", "LastModified": "1527579949"}, {"Comment": "BZ", "LastModified": "1528579949"}, {"Comment": "CZ", "LastModified": "1529579949"}]
(1 row) 

基于上述查询创建一个简单的SQL函数：

create or replace function update_comments(jsonb)
returns jsonb language sql as $$
    select jsonb_agg(value || jsonb_build_object('Comment', value->>'Comment' || 'Z'))
    from jsonb_array_elements($1)
$$;

使用该函数：

update my_table
set comments = update_comments(comments);

DbFiddle.

- klin

---

谢谢 @klin - 因此，请使用函数，不要使用 jsonb_set。 - Martin Flower