如何在 JSON 键的 postgres 更新语句中增加值 [英] How to increment value in postgres update statement on JSON key

问题描述

更新关系表时:

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

但是 JSON 中的等价物不起作用:

However the equivalent in JSON doesn't work:

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 "->" - 这是相当模糊的.

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

我该怎么做?

我正在使用 postgres 9.3.4

I am using postgres 9.3.4

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

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

如果你也喜欢这个功能，你可以投票.

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

推荐答案

基于 @joonas.fi 和 pozs 的答案，我想出了一个更漂亮"的解决方案

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

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

这篇关于如何在 JSON 键的 postgres 更新语句中增加值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！