CODEWITHSUNDEEP
sqlarraysjsonpostgresql
Botir Ziyatov
Botir Ziyatov

Reputation: 518

How to update JSONB array in postgres

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:

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

Upvotes: 0

Views: 1972

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51436

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
;

Upvotes: 3

Related Questions