Update or delete value in nested jsonb object

Question

I have two scenarios that I am trying to figure out. I have a jsonb column in a table with a structure like this:

   {
    "1": {
        "a": 0.084,
        "b": 0.084,
        "c": 0.084
    },
    "2": {
        "a": 0.078,
        "b": 0.0814,
        "c": 0.078
    },
    "3": {
        "a": 0.0928,
        "b": 0.0975,
        "c": 0.0975
    }
   }

If I wanted to change the value of "3":{"b":} how would I go about doing it? I tried looking at functions like jsonb_set(), but that seemed like I would have to copy the entire jsonb object and re-paste it in every time I wanted to update one value.

The second issue I want to solve is if I want to delete the entire "3":{} object, the only solution I can seem to find is using jsonb_set() again.

Thanks for your help!

Barbaros Özhan Barbaros Özhan · Accepted Answer · 2020-04-08T22:21:45

You can use jsonb_set() function with the related path '{3,b}' in order to update :

SELECT jsonb_set(jsonb_data, '{3,b}','0.1') 
  FROM tab

where 0.1 is just a sample value

Use #- operator in order to delete :

SELECT jsonb_data #- '{3}'
  FROM tab

Demo

Update or delete value in nested jsonb object

1 Answers