Update jsonb object in postgres

Question

One of my column is jsonb and have value in the format. The value of a single row of column is below.

{
    "835": {
        "cost": 0, 
        "name": "FACEBOOK_FB1_6JAN2020", 
        "email": "[email protected]", 
        "views": 0, 
        "clicks": 0, 
        "impressions": 0, 
        "campaign_state": "paused", 
        "processed":"in_progress", 
        "modes":["obj1","obj2"]
    }, 
    "876": {
        "cost": 0, 
        "name": "MARVEL_BLACK_WIDOW_4DEC2019", 
        "email": "[email protected]", 
        "views": 0, 
        "clicks": 0, 
        "impressions": 0, 
        "campaign_state": "paused", 
        "processed":"in_progress", 
        "modes":["obj1","obj2"]
    }
}

I want to update campaign_info(column name) column's the inner key "processed" and "models" of the campaign_id is "876".

I have tried this query:

update safe_vid_info 
set campaign_info -> '835' --> 'processed'='completed' 
where cid = 'kiywgh';

But it didn't work.

Any help is appreciated. Thanks.

Edit the question and show what you have tried already and explain what the exact problem with that was. — sticky bit
– sticky bit, Commented Mar 8, 2020 at 18:57
I have tried this query.. update safe_vid_info set campaign_info -> '835' --> 'processed'='completed' where cid = 'kiywgh'; But it didn't work. — Siddhant Kaushal
– Siddhant Kaushal, Commented Mar 8, 2020 at 19:11

GMB · Accepted Answer · 2020-03-08 23:23:06Z

22

Is this what you want?

jsonb_set(campaign_info, '{876,processed}', '"completed"')

This updates the value at path "876" > "processed" with value 'completed'.

In your update query:

update safe_vid_info 
set campaign_info = jsonb_set(campaign_info, '{876,processed}', '"completed"')
where cid = 'kiywgh';

edited Mar 8, 2020 at 23:23

answered Mar 8, 2020 at 23:07

GMB

224k25 gold badges103 silver badges151 bronze badges

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

3 Comments

Siddhant Kaushal Over a year ago

Thanks a lot.. That worked perfectly fine for me.. :) Also, could you please help if I want to update two keys in a single query.. For example, I want to update the value of "processed" to "completed" along with the value of "modes" to ["obj1","obj2","obj3,"obj4","obj5"] .. @GMB

Siddhant Kaushal Over a year ago

I tried this query. update safe_vid_info set campaign_info = jsonb_set(campaign_info, '{876,processed}', '"completed"'), campaign_info = jsonb_set(campaign_info, '{876,modes}', '["obj1", "obj2", "obj3", "obj4", "obj5"]') where cid = 'kiywgh';. But id didn't work.. It throws me an error that ERROR: multiple assignments to same column "campaign_info"

DrRelling Over a year ago

@SiddhantKaushal See the comment on stackoverflow.com/a/45483515 - if you want to do multiple updates in the same statement, you can nest jsonb_set into something like

set campaign_info = jsonb_set(jsonb_set(campaign_info, '{876,modes}', '["obj1", "obj2", "obj3", "obj4", "obj5"]'), '{876,processed}', '"completed"')

. It's not very readable, but it's an option if you absolutely have to get it down to one statement.

Collectives™ on Stack Overflow

Update jsonb object in postgres

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related