如何在Postgres 9.4中对JSONB类型的列执行更新操作

Question

查看Postgres 9.4数据类型JSONB的留档，我不知道如何对JSONB列进行更新。

JSONB类型和函数的文档：

http://www.postgresql.org/docs/9.4/static/functions-json.html http://www.postgresql.org/docs/9.4/static/datatype-json.html

举个例子，我有一个基本的表格结构：

CREATE TABLE test(id serial, data jsonb);

插入很容易，例如：

INSERT INTO test(data) values ('{"name": "my-name", "tags": ["tag1", "tag2"]}');

现在，我该如何更新“数据”列？这是无效的语法：

UPDATE test SET data->'name' = 'my-other-name' WHERE id = 1;

这是我错过的明显的地方吗？谢谢

Answer 1

这是在9.5中以Andrew Dunstan的jsonb_set形式出现的，它基于一个现有的扩展jsonbx，可以使用9.4

Answer 2

理想情况下，您不要将JSON文档用于要在关系数据库中操作的结构化常规数据。改为使用规范化的关系设计。

JSON主要用于存储不需要在RDBMS中操作的完整文档。相关的：

• 带有索引的JSONB与hstore

在Postgres中更新一行总是会写入整行的新版本。这是Postgres MVCC模型的基本原则。从性能的角度来看，您是更改JSON对象中的单个数据还是全部数据并不重要：必须编写该行的新版本。

因此，手册中的建议如下：

JSON数据存储在表中时，与任何其他数据类型一样，都要考虑并发控制。尽管存储大型文档是可行的，但请记住，任何更新都会获得整行的行级锁。考虑将JSON文档限制在可管理的大小，以减少更新事务之间的锁争用。理想情况下，JSON文档应该每个都代表一个原子数据，业务规则规定不能进一步细分为可以独立修改的较小数据。

其要点是：要修改JSON对象内部的任何内容，必须为列指定一个修改过的对象。Postgres除了存储功能外，还提供有限的html" target="_blank">方法来构建和操作json数据。自9.2版以来，随着每一个新版本的发布，工具库都有了实质性的增长。但原则仍然是：您必须始终为列指定一个完整的修改对象，Postgres总是为任何更新写入一个新的行版本。

如何使用Postgres 9.3或更高版本的工具的一些技术：

• 如何修改新PostgreSQL JSON数据类型中的字段

这个答案吸引的反对票和我所有其他答案加起来一样多。人们似乎不喜欢这个想法：对于常规数据来说，规范化设计更优越。克雷格·林格（Craig Ringer）的这篇优秀博文更详细地解释了：

• “PostgreSQL反模式：不必要的json/hstore动态列”

Laurenz Albe的另一篇博文，另一位官方博士后撰稿人，比如Craig和我自己：

• PostgreSQL中的JSON：如何正确使用

Answer 3

如果您能够升级到Postgresql 9.5，则可以使用jsonb_set命令，正如其他人所提到的。

在下面的代码中，我省略了SQL语句的简洁性；显然，你会想再加上一句。

更新名称：

UPDATE test SET data = jsonb_set(data, '{name}', '"my-other-name"');

更换标签（与添加或删除标签相反）：

UPDATE test SET data = jsonb_set(data, '{tags}', '["tag3", "tag4"]');

替换第二个标签（0-index）：

UPDATE test SET data = jsonb_set(data, '{tags,1}', '"tag5"');

附加标签( 只要标签少于999个，这项功能就可以使用；将参数999更改为1000或更高会生成错误 。在研究生9.5.3中似乎不再如此；可以使用更大的索引）：

UPDATE test SET data = jsonb_set(data, '{tags,999999999}', '"tag6"', true);

删除最后一个标记：

UPDATE test SET data = data #- '{tags,-1}'

复杂更新（删除最后一个标记，插入新标记，并更改名称）：

UPDATE test SET data = jsonb_set(
    jsonb_set(data #- '{tags,-1}', '{tags,999999999}', '"tag3"', true), 
    '{name}', '"my-other-name"');

需要注意的是，在这些示例中，您实际上并不是在更新JSON数据的单个字段。相反，您正在创建数据的临时修改版本，并将该修改版本分配回列。在实践中，结果应该是相同的，但记住这一点应该会使复杂的更新（如最后一个示例）更容易理解。

在这个复杂的示例中，有三个转换和三个临时版本：首先，删除最后一个标记。然后，通过添加一个新标记来转换该版本。接下来，通过更改名称字段来转换第二个版本。数据列中的值将替换为最终版本。