How to increment value in postgres update statement on JSON key

Question

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:

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

---

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.

Answer 1

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;