#StackBounty: #postgresql PgSql jsonb_set for all occurance of multiple keys in json array

Bounty: 50

I have below json

[
    {
        "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
    }

]

can update occurrence of a key like below

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
     )
;

But trying to update multiple keys like

"rows->>cols->>col_sec_json->>data->>class"

"rows->>cols->>col_sec_json->>to_date" 

"rows->>cols->>col_sec_json->>layout->>fontSize"

How can I update above to achieve this..

output should be like

[
    {
        "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": "new val",
                         "location": "0",
                        "unitForCurrent": ""
                    },
                    "theme": "defaultTheme",
                    "layout": {
                        "fontSize": 19,
                        "fontStyle": "Open Sans",
                        "textColor": "#545454",
                        "isHeadingAlignmentInherited": true
                    },
                    "org_id": 1,
                    "to_date": "2022-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": "new val",
                         "location": "0",
                        "unitForCurrent": ""
                    },
                    "theme": "defaultTheme",
                    "layout": {
                        "fontSize": 19,
                        "fontStyle": "Open Sans",
                        "textColor": "#545454",
                        "isHeadingAlignmentInherited": true
                    },
                    "org_id": 1,
                    "to_date": "2022-12-31",
                    "interval": "Yearly"
                  }
            }
                   
        ],
        "col_sec_id": 1626360791978,
        "row_cols_count": 2
    }

]


Get this bounty!!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.