0

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

1 Answer 1

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.

8
  • Thanks, it worked like charm.. now I need to understand this Aug 19, 2021 at 6:04
  • Have added a bit of explanation Aug 19, 2021 at 9:42
  • Thanks I got that from documents, trying to understand the order how this j1.value and j2 value working Aug 19, 2021 at 10:27
  • can you help me how to update any key of say col_sec_json.to_date to some date Aug 20, 2021 at 8:32
  • I'm not sure what you mean Aug 20, 2021 at 10:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.