如何在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的评论，我创建了一个功能请求:

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屋！