postgres jsonb update key value in array

Question

I have a table with a jsonb column with data from one row like

[
    {
        "a": [], 
        "c_id": 624, 
        "ps": [{"": 0, "pr": "73", "f": "M", "s": "M"}], 
        "g_n": "K L Mish", 
        "g_num": 1
    },
   {
        "a": [], 
        "c_id": 719, 
        "ps": [{"": 0, "pr": "65433", "f": "R", "s": "W"}], 
        "g_n": "S H Star", 
        "g_num": 2
    }, 
]

I want to update c_id in the table wherever it is 719 to 720. How can I do it?

I am using Postgres 12.1

This is kind of hard coded data, which is to be retrieved in very rare cases. Right now we need to update it as there were some duplicates with slightly different spellings. the c_id code change is required as we had to delete the duplicate entries. — Shh
– Shh, Commented Nov 25, 2019 at 13:35

S-Man · Accepted Answer · 2019-11-25 11:58:57Z

1

If it is only one single occurrence, you could do it using a Regular Expression:

Click: demo:db<>fiddle

UPDATE mytable
SET mydata = s.result::jsonb
FROM (
    SELECT
        regexp_replace(mydata::text, '(.*)("c_id"\s*:\s*)(719)(.*)','\1\2720\4') AS result
    FROM
        mytable
) s;

RegExp Groups:

(.*) All characters before the relevant key
("c_id"\s*:\s*) The relevant key incl. possible spaces
(719) The relevant value to be replaced
(.*) Everything after the relevant point

With \1\2720\4 you put the first two groups together, followed by the new value (instead of group 3) and the fourth group.

Disclaimer:

I fully agree with @a_horse_with_no_name: You should think about storing all values in separate and normalized tables/columns. You would gain a lot of benefits (much better search and update handling, indexing, performance, ...). If you need this JSON output, just handle it as output: Generate it when you need it, do not store it. Maybe a view could help a lot

answered Nov 25, 2019 at 11:58

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

2 Comments

Shh Over a year ago

This is a smart solution for my need. It's really needs to be done on a one off basis with max 2-3 code changes and I can run them separately. +1

Shh Over a year ago

We need a snap shot of data based on events. This is the reason we went for JSON .

Collectives™ on Stack Overflow

postgres jsonb update key value in array

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