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.

I don't think you can do what you want with the `update`. See this question: http://stackoverflow.com/questions/18209625/how-do-i-modify-fields-inside-the-new-postgresql-json-datatype. — Gordon Linoff, Sep 21 '14 at 12:55
@GordonLinoff hmm - it looks like out of CRUD, postgres only supports Read operations on properties of a JSON type? That appears to be a rather big gap in JSON support by postgres; are there any plans to add support for this, or should we stick to using postgres as a relational database (where Create/ Update/ Delete is required)? — bguiz, Sep 21 '14 at 13:12
_should we stick to using postgres as a relational database_? PostgreSQL __is__ a relational database. Notice that JSON is intended as a transport format and what PostgreSQL 9.3 stores is JSON (text) not an object. It will only be an object once evaluated. I think PostgreSQL has gone very far in reading properties, being a relational DBMS, not a procedural language like Javascript or Python. 9.4 has extended its JSON support and given the JSON demands from some (IMO misguided) users who want JSON replacing normalization and good design, I think it will likely go farther. — Clodoaldo Neto, Sep 21 '14 at 15:00

score 37 · Answer 1 · answered Jun 15 '17 at 09:19

37

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;

answered Jun 15 '17 at 09:19

Nour Wolf

2,140
25
24

score 11 · Answer 2 · edited Mar 17 '22 at 12:41

You can do this with jsonb, at least with 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:

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 ||'ing the jsonb objects:

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

SELECT * FROM users;

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

From the documentation:

jsonb || jsonb → jsonb

Concatenates two jsonb values. Concatenating two objects generates an object containing the union of their keys, taking the second object's value when there are duplicate keys.

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() ):

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. :)

Glen Thompson · Answer 3 · 2022-02-16T14:36:02.350

Nested JSONB data:

From:

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

To:

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

Use this:

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>';

Where table_name is your table name and data_col is your JSONB column

Notes:

If not obvious you can also decrement using - 1 etc for other operations.
The PostgreSQL docs for json operations are super helpful

How to increment value in postgres update statement on JSON key

3 Answers3

Linked