如何对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"]}');

现在，我要如何更新“data”列？这个语法是无效的：

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

这个有没有明显的文档，我是不是错过了什么？

- jvous

Answer 1

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

在以下每个SQL语句中，我已省略了where子句以缩短长度; 显然，您需要将其添加回去。

更新名称：

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

替换标签（而不是添加或删除标签）：

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

替换第二个标签（从0开始计数）：

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

添加标签（只要标签数量少于999个，这将起作用；将参数999更改为1000或更多会生成错误。在Postgres 9.5.3中似乎不再是这种情况；可以使用更大的索引）：

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

移除最后一个标签：

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

< p > 复杂更新（删除最后一个标签，插入一个新标签并更改名称）:

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

需要注意的是，在这些例子中，您实际上并没有更新 JSON 数据的单个字段。相反，您正在创建一个临时的、修改过的版本，并将该修改后的版本重新分配给列。实际上，结果应该是相同的，但牢记这一点可以使复杂的更新（例如最后一个示例）更加易于理解。

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

- Jimothy

---

如果您能展示如何像OP请求的那样更新表中的列，您将获得额外的奖励分数。 - chadrik

@chadrik：我添加了一个更复杂的示例。它并不完全符合您的要求，但应该可以给您一个想法。请注意，外部jsonb_set调用的输入是内部调用的输出，并且该内部调用的输入是data #- '{tags,-1}'的结果。即原始数据中删除了最后一个标签。 - Jimothy

@PranaySoni：为此，我可能会使用存储过程，或者如果开销不是问题，将数据带回来，在应用语言中进行操作，然后将其写回。听起来很沉重，但请记住，在我给出的所有示例中，您仍然没有更新JSON（B）中的单个字段：无论如何，您都要覆盖整个列。因此，存储过程实际上并没有什么不同。 - Jimothy

@Alex：是的，有点像黑客技巧。如果我说 {tags,0}，那就意味着“数组tags的第一个元素”，这样我就可以给该元素赋新值。通过使用大数而不是0，它不会替换数组中的现有元素，而是向数组添加一个新元素。但是，如果数组实际上有超过999,999,999个元素，则这将替换最后一个元素而不是添加一个新元素。 - Jimothy

如果字段包含null怎么办？看起来好像不起作用。例如，info jsonb字段为空： "UPDATE organizer SET info = jsonb_set(info, '{country}', '"FRA"') where info->>'country'::text IS NULL; "我得到了105条记录的更新，但是数据库中没有任何更改。 - stackdave

Answer 2

理想情况下，您不应该使用JSON文档来存储需要在关系型数据库内部进行操作的结构化、规则化数据。而是应该使用规范化的关系型设计。

JSON主要用于存储在RDBMS内部不需要进行操作的完整文档。相关链接:

• 带索引的JSONB vs. hstore

在Postgres中更新行总是会写入新版本的整个行。这是Postgres的MVCC模型的基本原则。从性能的角度来看，无论您更改JSON对象中的单个数据还是所有数据，都几乎没有区别：必须编写一行的新版本。

因此，在手册中给出了建议：

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

它的要点是: 要修改JSON对象中的任何内容，都必须将修改后的对象分配给该列。除了存储功能外，Postgres还提供了有限的构建和操作json数据的工具。自9.2版本以来，随着每个新版本的发布，这些工具的库存已经大大增加。但基本原则仍然是：您始终必须将完整的修改后的对象分配给该列，并且Postgres总是为任何更新编写新行版本。

一些处理Postgres 9.3或更高版本工具的技巧：

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

这个答案吸引了大约与我在SO上所有其他答案加起来一样多的踩。人们似乎不喜欢这个想法：规范化设计对于常规数据是优越的。Craig Ringer的这篇博客文章详细解释了这一点:

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

另一篇由Laurenz Albe撰写的博客文章，他和Craig以及我自己一样是Postgres官方贡献者:

• PostgreSQL中的JSON：正确使用方法

- Erwin Brandstetter

---

这个答案仅涉及JSON类型，不考虑JSONB。 - fiatjaf

@fiatjaf说：这个回答完全适用于数据类型json和jsonb。两者都存储JSON数据，jsonb以一种规范化的二进制形式存储，具有一些优势（和少量的缺点）。https://dev59.com/1mkv5IYBdhLWcg3wfA0P#10560761 任何文档类型在数据库内部频繁操作都不是一个好选择，而且也不仅限于这两种数据类型。对于小型、几乎没有结构化的JSON文档来说还可以接受。但是对于大型、嵌套的文档来说，这样做会很愚蠢。 - Erwin Brandstetter

“如何使用Postgres 9.3工具”的说明应该首先出现在您的答案中，因为它回答了所提出的问题。有时更新JSON以进行维护/模式更改等是有意义的，而不更新JSON的原因并不适用。 - Michael Wasser

好的，我会尽力以最清晰、简洁的语言翻译原文，并保持原意不变。请提供需要翻译的具体内容。 - Ppp

JSON支持在每个版本中都得到了改进，现在非常出色。它已经有一段时间了，并且对于某些应用非常有用。但是我的答案仍然完全适用 - 特别是对于这个问题所询问的“更新操作”，因为它解决了文档类型的一个原则性限制。对于常规数据，在一个更或多或少规范化的数据库模式中使用适当的列通常更加高效。这不会改变。Postgres项目给出了相应的建议，就像我上面引用的那样 - 在Postgres 13开发手册中没有被修改。 - Erwin Brandstetter

Answer 3

这个功能将在9.5版本中通过jsonb_set实现，由Andrew Dunstan开发，基于现有的扩展程序jsonbx，该扩展程序可以与9.4版本兼容。

- philofinfinitejest

---

这行代码的另一个问题是使用了jsonb_build_object()，因为x->key并不返回键值对，所以要填充需要使用jsonb_set(target, path, jsonb_build_object('key',x->key))。 - Peter Krauss

Answer 4

如果遇到此问题并且想要快速解决（并且卡在9.4.5或更早版本上），这里有一个可能的解决方案：

创建测试表

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

更新语句以更改jsonb值

UPDATE test 
SET data = replace(data::TEXT,': "my-name"',': "my-other-name"')::jsonb 
WHERE id = 1;

总的来说，被接受的答案是正确的，即您无法修改jsonb对象的单个部分（在9.4.5或更早版本中）; 但是，您可以将jsonb列强制转换为字符串（:: TEXT），然后操作该字符串并再次转换为jsonb格式（:: jsonb）。

有两个重要注意事项：

1. 这将替换JSON中所有值等于“my-name”的值（如果您有多个对象具有相同的值，则会这样）
2. 如果您使用9.5，则此方法不如jsonb_set有效率。

- Chad Capra

---

老天，我已经搜索了两个小时，想知道如何更新jsonb，以便替换所有\u0000空字符，你的例子给出了完整的图片。感谢这个！ - Joshua Robinson

看起来不错！顺便说一下，在你的例子中，replace 的第二个参数包括冒号，而第三个参数则不包括。看起来你的调用应该是 replace(data::TEXT, '"name":', '"my-other-name":')::jsonb。 - davidicus

谢谢@davidicus！非常抱歉更新很晚，但我感谢您为他人分享！ - Chad Capra

如果你选择这种方法，请非常小心地清洗你的用户输入，以防止它们污染你的数据。 - McKayla

Answer 5

更新“name”属性：

UPDATE test SET data=data||'{"name":"my-other-name"}' WHERE id = 1;

如果你想要删除例如 'name' 和 'tags' 属性：

UPDATE test SET data=data-'{"name","tags"}'::text[] WHERE id = 1;

- Arthur

Answer 6

这个问题是在 postgres 9.4 的情况下提出的， 然而，新观众注意，在 postgres 9.5 中， 对 JSONB 字段进行子文档创建/更新/删除操作是数据库原生支持的，无需使用扩展函数。

请参阅: JSONB modifying operators and functions。

- bguiz

Answer 7

我为自己编写了一个在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;

以下是样例用法：

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)

正如你所看到的，它会深入分析并在必要时更新/添加数值。

- J. Raczkiewicz

---

这在9.4版本中不起作用，因为jsonb_build_object是在9.5版本中引入的。 - Greg

@Greg 你说得对，我刚刚查了一下，我现在正在运行 PostgreSQL 9.5 - 这就是为什么它可以工作的原因。感谢你指出这一点 - 我的解决方案在9.4中无法使用。 - J. Raczkiewicz

@J.Raczkiewicz 函数非常好用！我该如何改进您的函数，以便在值不存在时添加插入？这在空列值（例如尚未具有 {} 的空列）的情况下是必需的。类似于 jsonb_set 函数中的 create if missing 布尔值。jsonb_set ( target jsonb, path text[], new_value jsonb [, create_if_missing boolean ] ) https://www.postgresql.org/docs/13/functions-json.html. - Steve

Answer 8

也许可以这样做： UPDATE test SET data = '"my-other-name"'::json WHERE id = 1; 这在我的情况下有效，其中data是json类型。

- Gianluigi Sartori

---

也适用于我的情况，postgresql 9.4.5。整个记录被重写，因此目前无法更新单个字段。 - kometen

Answer 9

这个问题的最佳答案有点过时。

现代的PostgreSQL有更加优雅的语法：

UPDATE test SET data['name'] = 'my-other-name' where id = 1;

当前文档：https://www.postgresql.org/docs/current/datatype-json.html#JSON-KEYS-ELEMENTS

- Clayton Gulick

Answer 10

整列更新对我有用：

UPDATE test SET data='{"name": "my-other-name", "tags": ["tag1", "tag2"]}' where id=1;

- anand