Updating PostgreSQL JSONB key value by adding 1 to existing key value

Question

Im getting error while updating JSON data

CREATE TABLE testTable
AS
  SELECT $${
    "id": 1,
    "value": 100
  }$$::jsonb AS jsondata;

and I want to update value to 101 by adding 1, after visiting many websites I found this statement

UPDATE testTable
SET jsondata = JSONB_SET(jsondata, '{value}', (jsondata->>'value')::int + 1);

but above one is giving error "cannot convert jsonb to int"

and my expected output is

{
    "id": 1,
    "value": 101
}

postgresql should not be reporting "cannot convert jsonb to int" then — Haleemur Ali
– Haleemur Ali, Commented Apr 10, 2017 at 19:13

Evan Carroll · Accepted Answer · 2017-04-10 18:55:23Z

6

Look at the signature of jsonb_set (using \df jsonb_set)

   Schema   |   Name    | Result data type |                                  Argument data types                                   |  Type  
------------+-----------+------------------+----------------------------------------------------------------------------------------+--------
 pg_catalog | jsonb_set | jsonb            | jsonb_in jsonb, path text[], replacement jsonb, create_if_missing boolean DEFAULT true | normal

What you want is this..

UPDATE testTable
  SET jsondata = jsonb_set(
    jsondata,
    ARRAY['value'],
    to_jsonb((jsondata->>'value')::int + 1)
  )
;

answered Apr 10, 2017 at 18:55

Evan Carroll

1

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Updating PostgreSQL JSONB key value by adding 1 to existing key value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related