如何在 Postgres 9.4 中对 JSONB 类型的列执行更新操作 [英] How to perform update operations on columns of type JSONB in Postgres 9.4
问题描述
查看 Postgres 9.4 数据类型 JSONB 的文档，我不太清楚如何对 JSONB 列进行更新.
Looking through the documentation for the Postgres 9.4 datatype JSONB, it is not immediately obvious to me how to do updates on JSONB columns.
JSONB 类型和函数的文档:
Documentation for JSONB types and functions:
http://www.postgresql.org/docs/9.4/静态/函数-json.htmlhttp://www.postgresql.org/docs/9.4/static/datatype-json.html
作为例子，我有这个基本的表结构:
As an examples, I have this basic table structure:
CREATE TABLE test(id serial, data jsonb);
插入很容易，例如:
INSERT INTO test(data) values ('{"name": "my-name", "tags": ["tag1", "tag2"]}');
现在，我将如何更新数据"列?这是无效的语法:
Now, how would I update the 'data' column? This is invalid syntax:
UPDATE test SET data->'name' = 'my-other-name' WHERE id = 1;
这是否记录在我遗漏的明显地方?谢谢.
Is this documented somewhere obvious that I missed? Thanks.
推荐答案
理想情况下，不要将 JSON 文档用于要在关系数据库中操作的结构化常规数据.改用规范化关系设计.
Ideally, you don't use JSON documents for structured, regular data that you want to manipulate inside a relational database. Use a normalized relational design instead.
JSON 主要用于存储不需要在 RDBMS 内操作的整个文档.相关:
JSON is primarily intended to store whole documents that do not need to be manipulated inside the RDBMS. Related:
在 Postgres 中更新一行总是写入 整个 行的新版本.这就是 Postgres 的 MVCC 模型的基本原理.从性能角度来看，更改 JSON 对象中的单个数据还是全部数据几乎无关紧要:必须编写新版本的行.
Updating a row in Postgres always writes a new version of the whole row. That's the basic principle of Postgres' MVCC model. From a performance perspective, it hardly matters whether you change a single piece of data inside a JSON object or all of it: a new version of the row has to be written.
因此手册中的建议:
JSON 数据受制于与以下相同的并发控制注意事项存储在表中时的任何其他数据类型.虽然存储量大文档是可行的，请记住，任何更新都需要整行的行级锁.考虑将 JSON 文档限制为可管理的大小，以减少更新之间的锁争用交易.理想情况下，每个 JSON 文档都应该代表一个原子商业规则规定的数据不能合理地进一步细分为更小的数据，可以独立修改.
JSON data is subject to the same concurrency-control considerations as any other data type when stored in a table. Although storing large documents is practicable, keep in mind that any update acquires a row-level lock on the whole row. Consider limiting JSON documents to a manageable size in order to decrease lock contention among updating transactions. Ideally, JSON documents should each represent an atomic datum that business rules dictate cannot reasonably be further subdivided into smaller datums that could be modified independently.
其要点:要修改 JSON 对象内的任何内容，您必须将修改后的对象分配给该列.除了存储能力之外，Postgres 还提供了有限的方法来构建和操作 json 数据.自 9.2 版以来，随着每个新版本的发布，工具库都大幅增加.但原则仍然存在:您总是必须为列分配一个完整的修改对象，并且 Postgres 总是为任何更新编写一个新的行版本.
The gist of it: to modify anything inside a JSON object, you have to assign a modified object to the column. Postgres supplies limited means to build and manipulate json data in addition to its storage capabilities. The arsenal of tools has grown substantially with every new release since version 9.2. But the principal remains: You always have to assign a complete modified object to the column and Postgres always writes a new row version for any update.
如何使用 Postgres 9.3 或更高版本的工具的一些技巧:
Some techniques how to work with the tools of Postgres 9.3 or later:
这个答案吸引了与我在 SO together 上的所有其他答案一样多的反对票.人们似乎不喜欢这个想法:规范化设计对于常规数据更胜一筹.克雷格·林格 (Craig Ringer) 撰写的这篇出色的博文更详细地解释了:
This answer has attracted about as many downvotes as all my other answers on SO together. People don't seem to like the idea: a normalized design is superior for regular data. This excellent blog post by Craig Ringer explains in more detail:
Laurenz Albe 的另一篇博文，另一位 Postgres 官方贡献者，如 Craig 和我自己:
Another blog post by Laurenz Albe, another official Postgres contributor like Craig and myself:
这篇关于如何在 Postgres 9.4 中对 JSONB 类型的列执行更新操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
