CODEWITHSUNDEEP
sqljsonpostgresqlsql-updatepostgresql-9.3
bguiz
bguiz

Reputation: 28587

How to increment value in postgres update statement on JSON key

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.

Upvotes: 20

Views: 14578

Answers (3)

joonas.fi
joonas.fi

Reputation: 8226

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

Upvotes: 11

Glen Thompson
Glen Thompson

Reputation: 9996

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

Upvotes: 2

Nour Wolf
Nour Wolf

Reputation: 2180

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;

Upvotes: 40

Related Questions