如何在PostgreSQL的更新语句中增加JSON键的值

Question

当更新关系表时：

CREATE TABLE foo ( id serial primary key, credit numeric);
UPDATE foo SET bar = bar + $1 WHERE id = $2;

然而，在JSON中的等价写法却无法工作：

CREATE TABLE foo ( id serial primary key, data json);
UPDATE foo SET data->'bar' = data->'bar' + $1 WHERE id = $2;

我得到的错误是error: syntax error at or near "->" - 这相当模糊。

我该怎么办？

我正在使用Postgres 9.3.4。

---

根据@GordonLinoff的评论，我已经创建了一个功能请求：https://postgresql.uservoice.com/forums/21853-general/suggestions/6466818-create-update-delete-on-json-keys。

如果您也想要这个功能，可以投票支持。

- bguiz

---

我认为你不能用“update”实现你想要的功能。请参考这个问题：https://dev59.com/-GMl5IYBdhLWcg3wr4nn。 - Gordon Linoff

@GordonLinoff 嗯 - 看起来在CRUD中，Postgres只支持对JSON类型属性的读取操作？这似乎是Postgres在JSON支持方面的一个相当大的缺口；是否有计划添加对此的支持，或者我们应该继续将Postgres用作关系型数据库（其中需要创建/更新/删除）？ - bguiz

我们应该坚持使用PostgreSQL作为关系型数据库吗？PostgreSQL是一种关系型数据库。请注意，JSON旨在作为传输格式，而PostgreSQL 9.3存储的是JSON（文本），而不是对象。只有在评估后它才会成为一个对象。我认为PostgreSQL在阅读属性方面已经走得很远了，它是一个关系型DBMS，而不是像Javascript或Python这样的过程化语言。9.4扩展了其JSON支持，并考虑到一些（在我看来误导的）用户对JSON替换规范化和良好设计的需求，我认为它可能会走得更远。 - Clodoaldo Neto

Answer 1

根据@joonas.fi和pozs的回答，我提出了一个稍微更加“美观”的解决方案

UPDATE foo 
SET data = jsonb_set(data, '{bar}', (COALESCE(data->>'bar','0')::int + 1)::text::jsonb)
WHERE id = 1;

- Nour Wolf

Answer 2

你可以使用jsonb来实现这一点，至少在Postgres 9.5.2中是可以的。 给定以下表格：

CREATE TABLE users (id INT, counters JSONB NOT NULL DEFAULT '{}');

使用示例数据：

INSERT INTO users (id, counters) VALUES (1, '{"bar": 0}');

SELECT * FROM users;

 id |    counters
----+------------
  1 | {"bar": 0}

您可以原子化地增加 JSON 中的“bar”键：

UPDATE users SET counters = counters || CONCAT('{"bar":', COALESCE(counters->>'bar','0')::int + 1, '}')::jsonb WHERE id = 1;

SELECT * FROM users;

 id |    counters
----+------------
  1 | {"bar": 1}

虽然不够美观，但它能正常运作。

下面将其分解为几个步骤：

通过对 jsonb 对象执行 || 运算符，您可以将键设置为显式值：

UPDATE users SET counters = counters || '{"bar": 314}'::jsonb WHERE id = 1;

SELECT * FROM users;

 id |     counters
----+--------------
  1 | {"bar": 314}

来自文档:

jsonb || jsonb → jsonb

连接两个jsonb值。连接两个对象会生成一个包含它们键的并集的对象，并在存在重复键时，以第二个对象的值为准。

现在只需使用CONCAT()动态构建字符串，同时演示如何递增（27），一个未定义的键（使用COALESCE()默认初始值）：

UPDATE users SET counters = counters || CONCAT('{"foo":', COALESCE(counters->>'foo','0')::int + 27, '}')::jsonb WHERE id = 1;

SELECT * FROM users;

 id |          counters
----+-------------------------
  1 | {"bar": 314, "foo": 27}

Bob's your uncle. :)

-->

一切都搞定了。 :)

- joonas.fi

Answer 3

嵌套的JSONB数据：

原始数据：

table_name.data_col = {"a": {"b": {"c": 1}}} // JSONB

转换后的结果：

table_name.data_col = {"a": {"b": {"c": 2}}} // JSONB

使用此方法：

UPDATE 
  table_name 
SET 
  data_col = jsonb_set(
    data_col, 
    '{a,b,c}', 
    (
      COALESCE(
        data_col#>'{a,b,c}', '0'
      ):: int + 1
    ):: text :: jsonb
  ) 
WHERE 
  id = '<id>';

其中table_name是您的表名，data_col是您的JSONB列

注意：

• 如果不明显，您也可以使用-1等进行其他操作。
• PostgreSQL文档中的json操作非常有帮助

- Glen Thompson