postgres jsonb update key value in array

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

Answer

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)(.*)','127204') AS result
    FROM
        mytable
) s;

RegExp Groups:

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

With 127204 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