updating postgres jsonb column

Question

I have below json string in my table column which is of type jsonb,

{
    "abc": 1,
    "def": 2
}

i want to remove the "abc" key from it and insert "mno" with some default value. i followed the below approcach for it.

UPDATE books SET books_desc = books_desc - 'abc';
UPDATE books SET books_desc = jsonb_set(books_desc, '{mno}', '5');

and it works.

Now i have another table with json as below,

{
    "a": {
        "abc": 1,
        "def": 2
    },
    "b": {
        "abc": 1,
        "def": 2
    }
}

Even in this json, i want to do the same thing. take out "abc" and introduce "mno" with some default value. Please help me to achieve this.

The keys "a" and "b" are dynamic and can change. But the values for "a" and "b" will always have same keys but values may change. I need a generic logic.

Requirement 2:

abc:true should get converted to xyz:1.

abc:false should get converted to xyz:0.

i wonder why you choose for JSON here? As the example data is more easy to be stored in SQL tables to be selected, updated or deleted. .. As it looks that JSON is a bit misplaced to be used here. — Raymond Nijland
– Raymond Nijland, Commented Jun 24, 2019 at 9:23
But thats the kind of json i am recieving from the external module. — vinod hy
– vinod hy, Commented Jun 24, 2019 at 9:32
Fair enough.. Can you read Why should I provide a Minimal Reproducible Example for a very simple SQL query? for providing better example data and expected results — Raymond Nijland
– Raymond Nijland, Commented Jun 24, 2019 at 9:40

S-Man · Accepted Answer · 2019-06-24 10:35:40Z

1

demo:db<>fiddle

Because of a possible variety of your JSON keys it might be complicated to generate a common query. This is because you need to give the path within the json_set() function. But without actual values it would be hard.

A simple work-around is using the regexp_replace() function on the text representation of the JSON string to replace the relevant objects.

UPDATE my_table
SET my_data = 
    regexp_replace(my_data::text, '"abc"\s*:\s*\d+', '"mno":5', 'g')::jsonb

answered Jun 24, 2019 at 10:35

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

1 Comment

vinod hy Over a year ago

It works. Thanks for the help. I have also answered for the requirement 2 which i mentioned in the question. Please go through my answer and provide your comments

S-Man · Accepted Answer · 2019-06-27 07:00:32Z

1

For added requirement 2:

I wrote the below query based on already given solution:

UPDATE books
SET book_info = 
    regexp_replace(book_info::text, '"abc"\s*:\s*true', '"xyz":1', 'g')::jsonb; 

UPDATE books
SET book_info = 
    regexp_replace(book_info::text, '"abc"\s*:\s*false', '"xyz":0', 'g')::jsonb;

edited Jun 27, 2019 at 7:00

S-Man

24k9 gold badges51 silver badges78 bronze badges

answered Jun 27, 2019 at 5:44

vinod hy

8952 gold badges16 silver badges30 bronze badges

Collectives™ on Stack Overflow

updating postgres jsonb column

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related