PgSql jsonb_set for all occurance in json array

Question

I have a json like below

[
    {
        "rows": [
            {
                "col_class": "col50",
                "col_sec_id": 1626361165906,
                "col_sec_json": null
            },
            {
                "col_class": "col50",
                "col_sec_id": 1626361165907,
                "col_sec_json": {
                    "id": 1626361165907,
                    "data": {
                        "class": "0",
                         "location": "0",
                        "unitForCurrent": ""
                    },
                    "theme": "defaultTheme",
                    "layout": {
                        "fontSize": 14,
                        "fontStyle": "Open Sans",
                        "textColor": "#545454",
                        "isHeadingAlignmentInherited": true
                    },
                    "org_id": 1,
                    "to_date": "2020-12-31",
                    "interval": "Yearly"
                  }
            }
                   
        ],
        "col_sec_id": 1626360791978,
        "row_cols_count": 2
    },
 {
        "rows": [
            {
                "col_class": "col50",
                "col_sec_id": 1626361165906,
                "col_sec_json": null
            },
            {
                "col_class": "col50",
                "col_sec_id": 1626361165907,
                "col_sec_json": {
                    "id": 1626361165907,
                    "data": {
                        "class": "0",
                         "location": "0",
                        "unitForCurrent": ""
                    },
                    "theme": "defaultTheme",
                    "layout": {
                        "fontSize": 14,
                        "fontStyle": "Open Sans",
                        "textColor": "#545454",
                        "isHeadingAlignmentInherited": true
                    },
                    "org_id": 1,
                    "to_date": "2020-12-31",
                    "interval": "Yearly"
                  }
            }
                   
        ],
        "col_sec_id": 1626360791978,
        "row_cols_count": 2
    }

]

I want to update all ocurance of class inside rows->>cols->>col_sec_json->>data->>class to new value

I can update any specific item like below

select  jsonb_set(jdata, '{0,rows,1,col_sec_json,data,class}', '"new_value"')

But in above query I need to be like

select  jsonb_set(jdata, '{**---dyanamic for all rows ---** ,rows,**--- dynaic all colssec-json in each element of a rows---**,col_sec_json,data,class}', '"new_value"')

I tried alot but could not find,

Any help, highly appreciable...

Thanks

Answer 1

The easiest way to do this is to deconstruct the JSON, modify each array element, then rebuild the array

SELECT
    (SELECT jsonb_agg(
        jsonb_set(j1.value, '{rows}',
            (
             SELECT jsonb_agg(jsonb_set(j2.value, '{col_sec_json,data,class}', '"new_value"'))
             FROM jsonb_array_elements(j1.value->'rows') j2
            )
        ))
     FROM jsonb_array_elements(t.jdata) j1
     )
FROM YourTable t

Or as an UPDATE

UPDATE YourTable t
SET jdata = 
    (SELECT jsonb_agg(
        jsonb_set(j1.value, '{rows}',
            (
             SELECT jsonb_agg(jsonb_set(j2.value, '{col_sec_json,data,class}', '"new_value"'))
             FROM jsonb_array_elements(j1.value->'rows') j2
            )
        ))
     FROM jsonb_array_elements(t.jdata) j1
     )
;

jsonb_array_elements is used to deconstruct a JSON array into separate rows of JSON objects. Then jsonb_set modifies the object, and jsonb_agg aggregates it back into an array.