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 · Accepted Answer · 2020-04-08 22:25:02Z

4

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

edited Apr 8, 2020 at 22:25

answered Apr 8, 2020 at 22:21

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

2 Comments

Christopher Carlisle Over a year ago

Thanks! That works out really well and does exactly what I need it to. Is it possible to do that with multiple values in the same object? Like if I wanted to set the value for A, B and C at the same time? Thanks again.

Barbaros Özhan Over a year ago

you're welcome @ChristopherCarlisle. Not possible with multiple values within the same object.

Collectives™ on Stack Overflow

Update or delete value in nested jsonb object

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related