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.