postgresql 如何在 Postgres 9.4 中对 JSONB 类型的列执行更新操作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26703476/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to perform update operations on columns of type JSONB in Postgres 9.4
提问by jvous
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.
查看 Postgres 9.4 数据类型 JSONB 的文档，我不太清楚如何对 JSONB 列进行更新。
Documentation for JSONB types and functions:
JSONB 类型和函数的文档：
http://www.postgresql.org/docs/9.4/static/functions-json.htmlhttp://www.postgresql.org/docs/9.4/static/datatype-json.html
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.
这是否记录在我错过的明显地方？谢谢。
采纳答案by Erwin Brandstetter
Ideally, you don't use JSON documents for structured, regular data that you want to manipulate inside a relational database. Use a normalized relational designinstead.
理想情况下，您不要将 JSON 文档用于要在关系数据库中操作的结构化常规数据。改用规范化的关系设计。
JSON is primarily intended to store whole documents that do not need to be manipulated inside the RDBMS. Related:
JSON 主要用于存储不需要在 RDBMS 内操作的整个文档。有关的：
Updating a row in Postgres always writes a new version of the wholerow. 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.
在 Postgres 中更新一行总是写入整行的新版本。这就是Postgres 的 MVCC 模型的基本原理。从性能的角度来看，是更改 JSON 对象中的单个数据还是全部数据几乎无关紧要：必须编写新版本的行。
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.
JSON 数据在存储在表中时，与任何其他数据类型一样，需要遵守相同的并发控制注意事项。尽管存储大型文档是可行的，但请记住，任何更新都会获取整行的行级锁。考虑将 JSON 文档限制为可管理的大小，以减少更新事务之间的锁争用。理想情况下，JSON 文档应该每个都代表一个原子数据，业务规则规定不能合理地进一步细分为可以独立修改的较小数据。
The gist of it: to modify anythinginside a JSON object, you have to assign a modified object to the column. Postgres supplies limited means to build and manipulate jsondata 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 alwayshave to assign a complete modified object to the column and Postgres always writes a new row version for any update.
它的要点：要修改JSON 对象内的任何内容，您必须将修改后的对象分配给该列。json除了存储能力之外，Postgres 还提供了有限的方法来构建和操作数据。自 9.2 版以来，随着每个新版本的发布，工具库都大幅增加。但原则仍然存在：您总是必须为列分配一个完整的修改对象，并且 Postgres 总是为任何更新编写一个新的行版本。
Some techniques how to work with the tools of Postgres 9.3 or later:
如何使用 Postgres 9.3 或更高版本的工具的一些技巧：
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:
这个答案已经吸引了许多downvotes上所以我所有其他的答案在一起。人们似乎不喜欢这个想法：规范化设计对于非动态数据更胜一筹。Craig Ringer 的这篇出色的博客文章更详细地解释了：
回答by Jimothy
If you're able to upgrade to Postgresql 9.5, the jsonb_setcommand is available, as others have mentioned.
如果您能够升级到 Postgresql 9.5，则该jsonb_set命令可用，正如其他人所提到的。
In each of the following SQL statements, I've omitted the whereclause for brevity; obviously, you'd want to add that back.
在以下每个 SQL 语句中，where为了简洁起见，我都省略了子句；显然，你想把它加回来。
Update name:
更新名称：
UPDATE test SET data = jsonb_set(data, '{name}', '"my-other-name"');
Replace the tags (as oppose to adding or removing tags):
替换标签（与添加或删除标签相反）：
UPDATE test SET data = jsonb_set(data, '{tags}', '["tag3", "tag4"]');
Replacing the second tag (0-indexed):
替换第二个标签（0 索引）：
UPDATE test SET data = jsonb_set(data, '{tags,1}', '"tag5"');
Append a tag (this will work as long as there are fewer than 999 tags; changing argument 999 to 1000 or above generates an error. This no longer appears to be the case in Postgres 9.5.3; a much larger index can be used):
附加标签（只要标签少于 999 个，这将起作用；将参数 999 更改为 1000 或更高会产生错误。在 Postgres 9.5.3 中不再出现这种情况；可以使用更大的索引） ：
UPDATE test SET data = jsonb_set(data, '{tags,999999999}', '"tag6"', true);
Remove the last tag:
删除最后一个标签：
UPDATE test SET data = data #- '{tags,-1}'
Complex update (delete the last tag, insert a new tag, and change the name):
复杂更新（删除最后一个标签，插入一个新标签，并更改名称）：
UPDATE test SET data = jsonb_set(
jsonb_set(data #- '{tags,-1}', '{tags,999999999}', '"tag3"', true),
'{name}', '"my-other-name"');
It's important to note that in each of these examples, you're not actually updating a single field of the JSON data. Instead, you're creating a temporary, modified version of the data, and assigning that modified version back to the column. In practice, the result should be the same, but keeping this in mind should make complex updates, like the last example, more understandable.
请务必注意，在这些示例中的每一个中，您实际上并未更新 JSON 数据的单个字段。相反，您正在创建数据的临时修改版本，并将修改后的版本分配回列。在实践中，结果应该是相同的，但记住这一点应该会使复杂的更新，就像最后一个例子一样，更容易理解。
In the complex example, there are three transformations and three temporary versions: First, the last tag is removed. Then, that version is transformed by adding a new tag. Next, the second version is transformed by changing the namefield. The value in the datacolumn is replaced with the final version.
在复杂的例子中，有三个转换和三个临时版本：首先，最后一个标签被删除。然后，通过添加新标签来转换该版本。接下来，通过更改name字段来转换第二个版本。data列中的值将替换为最终版本。
回答by philofinfinitejest
This is coming in 9.5 in the form of jsonb_setby Andrew Dunstanbased on an existing extension jsonbxthat does work with 9.4
这是在 9.5 中以jsonb_set的形式由Andrew Dunstan基于现有的扩展jsonbx 推出的，该扩展可与 9.4 一起使用
回答by Chad Capra
For those that run into this issue and want a very quick fix (and are stuck on 9.4.5 or earlier), here is what I did:
对于那些遇到此问题并希望快速修复的人（并且卡在 9.4.5 或更早版本上），这是我所做的：
Creation of test table
创建测试表
CREATE TABLE test(id serial, data jsonb);
INSERT INTO test(data) values ('{"name": "my-name", "tags": ["tag1", "tag2"]}');
Update statement to change name of jsonb property
更新语句以更改 jsonb 属性的名称
UPDATE test
SET data = replace(data::TEXT,'"name":','"my-other-name":')::jsonb
WHERE id = 1;
Ultimately, the accepted answer is correct in that you cannot modify an individual piece of a jsonb object (in 9.4.5 or earlier); however, you can cast the jsonb object to a string (::TEXT) and then manipulate the string and cast back to the jsonb object (::jsonb).
最终，接受的答案是正确的，因为您不能修改 jsonb 对象的单个部分（在 9.4.5 或更早版本中）；但是，您可以将 jsonb 对象转换为字符串 (::TEXT)，然后操作该字符串并转换回 jsonb 对象 (::jsonb)。
There are two important caveats
有两个重要的警告
- this will replace all properties called "name" in the json (in the case you have multiple properties with the same name)
- this is not as efficient as jsonb_set would be if you are using 9.5
- 这将替换 json 中所有名为“name”的属性（如果您有多个同名的属性）
- 如果您使用的是 9.5，这不如 jsonb_set 有效
With that said, I came across a situation where I had to update the schema for content in the jsonb objects and this was the simplest way to accomplish exactly what the original poster was asking.
话虽如此，我遇到了一种情况，我必须更新 jsonb 对象中内容的架构，这是完成原始海报要求的最简单的方法。
回答by bguiz
This question was asked in the context of postgres 9.4, however new viewers coming to this question should be aware that in postgres 9.5, sub-document Create/Update/Delete operations on JSONB fields are natively supported by the database, without the need for extension functions.
这个问题是在 postgres 9.4 的上下文中提出的，但是新的观众应该知道在 postgres 9.5 中，数据库本身支持对 JSONB 字段的子文档创建/更新/删除操作，无需扩展职能。
See: JSONB modifying operators and functions
请参阅：JSONB 修改运算符和函数
回答by Arthur
update the 'name' attribute:
更新“名称”属性：
UPDATE test SET data=data||'{"name":"my-other-name"}' WHERE id = 1;
and if you wanted to remove for example the 'name' and 'tags' attributes:
如果您想删除例如 'name' 和 'tags' 属性：
UPDATE test SET data=data-'{"name","tags"}'::text[] WHERE id = 1;
回答by J. Raczkiewicz
I wrote small function for myself that works recursively in Postgres 9.4. I had same problem (good they did solve some of this headache in Postgres 9.5). Anyway here is the function (I hope it works well for you):
我为自己编写了在 Postgres 9.4 中递归运行的小函数。我遇到了同样的问题（很好，他们确实解决了 Postgres 9.5 中的一些问题）。无论如何，这是功能（我希望它对您有用）：
CREATE OR REPLACE FUNCTION jsonb_update(val1 JSONB,val2 JSONB)
RETURNS JSONB AS $$
DECLARE
result JSONB;
v RECORD;
BEGIN
IF jsonb_typeof(val2) = 'null'
THEN
RETURN val1;
END IF;
result = val1;
FOR v IN SELECT key, value FROM jsonb_each(val2) LOOP
IF jsonb_typeof(val2->v.key) = 'object'
THEN
result = result || jsonb_build_object(v.key, jsonb_update(val1->v.key, val2->v.key));
ELSE
result = result || jsonb_build_object(v.key, v.value);
END IF;
END LOOP;
RETURN result;
END;
$$ LANGUAGE plpgsql;
Here is sample use:
这是示例使用：
select jsonb_update('{"a":{"b":{"c":{"d":5,"dd":6},"cc":1}},"aaa":5}'::jsonb, '{"a":{"b":{"c":{"d":15}}},"aa":9}'::jsonb);
jsonb_update
---------------------------------------------------------------------
{"a": {"b": {"c": {"d": 15, "dd": 6}, "cc": 1}}, "aa": 9, "aaa": 5}
(1 row)
As you can see it analyze deep down and update/add values where needed.
如您所见，它会深入分析并在需要时更新/添加值。
回答by Gianluigi Sartori
Maybe: UPDATE test SET data = '"my-other-name"'::json WHERE id = 1;
也许：UPDATE test SET data = '"my-other-name"'::json WHERE id = 1;
It worked with my case, where data is a json type
它适用于我的情况，其中数据是 json 类型
回答by John Clark
Matheus de Oliveira created handy functions for JSON CRUD operations in postgresql. They can be imported using the \i directive. Notice the jsonb fork of the functions if jsonb if your data type.
Matheus de Oliveira 为 postgresql 中的 JSON CRUD 操作创建了方便的函数。它们可以使用 \i 指令导入。如果您的数据类型为 jsonb，请注意函数的 jsonb 分支。
9.3 json https://gist.github.com/matheusoliveira/9488951
9.3 json https://gist.github.com/matheusoliveira/9488951
9.4 jsonb https://gist.github.com/inindev/2219dff96851928c2282
9.4 jsonb https://gist.github.com/inindev/2219dff96851928c2282
