PostgreSQL - Update inner json

Question

I have a column jdata of type jsonb inside a table 'JTABLE'. A sample jdata looks like this :

{
 "id" : 12,
 "address" : {
     "houseName": {
        "name" : "Jackson",
         "lang" : "ENG"
     } 
  } 
}

How can i query to update the lang to anotherValue for this?

I tried this and it doesn't seem to work :

UPDATE JTABLE SET jdata -> 'address'->'houseName'-> 'lang' = '"DEU"' where jdata->>'id' = '12';

This doesn't work! Any help?

EDIT:

This overwrites my value and i get this when i run :

{
 "id" : 12,
 "address" : {
     "houseName": {
         "lang" : "DEU"
     } 
  } 
}

I lost key name.

I'm trying this query now :

SELECT jsonb_set(jdata, 'address,houseName}', '{"lang":"DEU"}'::jsonb) FROM JTABLE where jdata->>'id' = '12';

Answer 1

Your path is wrong, the 2nd statement should be a path to the json key you wish to update.

The query to view your update should look like :

SELECT jsonb_set(jdata, '{address,houseName,lang}', '"DEU"') FROM JTABLE where jdata->'id' = '12';

The final query to update :

UPDATE JTABLE SET jdata = jsonb_set(jdata, '{address,houseName,lang}', '"DEU"') WHERE jdata->'id' = '12';

Also don't type cast a record to `jsonb.