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

When updating a relational table:

更新关系表时:

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

However the equivalent in JSON doesn't work:

但是,JSON中的等效项不起作用:

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

The error I get is error: syntax error at or near "->" - which is rather ambiguous.

我得到的错误是错误:语法错误在“ - >”或附近 - 这是相当模糊的。

How do I do this?

我该怎么做呢?

I am using postgres 9.3.4

我正在使用postgres 9.3.4

In light of @GordonLinoff's comment below, I have created a feature request: https://postgresql.uservoice.com/forums/21853-general/suggestions/6466818-create-update-delete-on-json-keys

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

You can vote on it if you would like this feature too.

如果您也想要此功能,可以对其进行投票。

2 个解决方案

#1

You can do this with jsonb, at least with Postgres 9.5.2.

您可以使用jsonb执行此操作,至少使用Postgres 9.5.2。

Given the following table:

鉴于下表:

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

With sample data:

使用示例数据:

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

SELECT * FROM users;

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

You can increment "bar" key in JSON atomically:

您可以原子地增加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}

It's not beautiful but it works.

它不漂亮,但它的工作原理。

Here it is broken down in steps:

这里按步骤分解:

You can set a key in jsonb to an explicit value by OR'ing the jsonb objects:

您可以通过对jsonb对象进行OR运算将jsonb中的键设置为显式值:

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

SELECT * FROM users;

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

Now all that's left to do is build the string dynamically with the help of CONCAT(), at the same time demonstrating incrementing (by 27) an undefined key (defaulting initial value with help of COALESCE() ):

现在剩下要做的就是在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. :)

鲍勃是你的叔叔。 :)

#2

Based on @joonas.fi's and pozs's answers, I came up with a slightly more 'beautiful' solution

根据@ joonas.fi和pozs的答案,我想出了一个更“美丽”的解决方案

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

#1