How to update JSONB array in postgres

Question

I have a table with following definition:

CREATE TABLE USER_CONFIGURATIONS (
  ID         BIGSERIAL PRIMARY KEY,
  DATA       JSONB
);

I have a data field that looks like this :

[
    {
        "user_id": 1,       
        "user_name": "demo_user", 
        "is_manager": 1,        
        "options": [
            {
                "phone":{
                    "home":"XXXXXXX",
                    "work":"XXXXXXX"
                },
                "address":{
                    "home":"XXXXXXX",
                    "work":"XXXXXXX"
                }
            }
        ]
    },
    ...
]

questions:

how to update "user_name" ?
how to update "options->phone->home" ?

UPDATE USER_CONFIGURATIONS SET DATA = jsonb_set(...) WHERE ...user_id=1;

postgres 9.6 version. i tried with jsonb_set() but not wokring

Vao Tsun · Accepted Answer · 2018-02-22 08:50:08Z

3

https://www.db-fiddle.com/f/4ZYZiuJr4QgfNkzyTCeT1X/1

just run it twice:

update USER_CONFIGURATIONS
set data =  
jsonb_set(
  jsonb_set(
    data,'{0,"user_name"}','"blah"'
  ), '{0,"options",0,"phone","home"}','999999'
)
where id =1
;

answered Feb 22, 2018 at 8:50

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

Collectives™ on Stack Overflow

How to update JSONB array in postgres

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related