如何在Postgres 9.4中对JSONB类型的列执行更新操作 [英] How to perform update operations on columns of type JSONB in Postgres 9.4
问题描述
仔细阅读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;
这是我错过的明显地方吗?谢谢.
理想情况下，您不要将JSON文档用于要在关系数据库中处理的结构化常规数据.改用归一化的关系设计.
JSON主要用于存储不需要在RDBMS内部进行操作的整个文档.相关:
在Postgres中更新一行将始终写入整个行的新版本.这是 Postgres的MVCC模型的基本原理.从性能的角度来看，无论是更改JSON对象中的单个数据还是全部数据都无关紧要:必须编写该行的新版本.
因此，手册中的建议 :
JSON数据应遵循与以下相同的并发控制注意事项 存储在表中的任何其他数据类型.虽然存储量大 文档是可行的，请记住，任何更新都需要 整行上的行级锁定.考虑将JSON文档限制为 可管理的大小，以减少更新之间的锁争用 交易.理想情况下，每个JSON文档都应代表一个原子 业务规则规定的数据不能合理地进一步扩展 细分为较小的基准，可以独立进行修改.
要点:要修改JSON对象中的任何内容，您必须将修改后的对象分配给该列.除存储功能外，Postgres还提供了有限的方法来构建和处理json数据.自9.2版以来，随着每个新版本的发布，工具库已大大增加.但是原则仍然存在:您总是必须为该列分配一个完整的修改后的对象，而Postgres总是为任何更新编写新的行版本.
一些使用Postgres 9.3或更高版本的工具的技术:
这个答案吸引了与我在 上所有其他答案一样多的反对意见.人们似乎不喜欢这个想法:归一化设计对于非动态数据更胜一筹.克雷格·林格(Craig Ringer)撰写的这篇出色的博客文章更详细地解释了:
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.
Documentation for JSONB types and functions:
http://www.postgresql.org/docs/9.4/static/functions-json.html http://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);
Inserting is easy, as in:
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.
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 is primarily intended to store whole documents that do not need to be manipulated inside the RDBMS. Related:
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.
Thus the advice in the manual:
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.
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.
Some techniques how to work with the tools of Postgres 9.3 or later:
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 non-dynamic data. This excellent blog post by Craig Ringer explains in more detail:
这篇关于如何在Postgres 9.4中对JSONB类型的列执行更新操作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
