CODEWITHSUNDEEP
postgresqlpostgresql-9.5
Harish
Harish

Reputation: 161

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

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
}

Upvotes: 1

Views: 1554

Answers (1)

Evan Carroll
Evan Carroll

Reputation: 1

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

Upvotes: 6

Related Questions