0

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
    }

]

Update

I am able to do this using nested jsonb_set like below

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

But I have doubt regarding the performance, I have seen some places that It can be done using || string concatenation operator, but when I tried, it was replacing original json object with new object with the keys that I replace.

3
  • What exactly do you want to update them with? Aug 20, 2021 at 11:19
  • any dummy value Aug 20, 2021 at 11:26
  • let's say class with "new val" ,, to_date wth "2022-12-31" and fontSize like 19 Aug 20, 2021 at 11:29

1 Answer 1

0

Provided your json is stored in table t_jsonb2 column j:

CREATE OR REPLACE FUNCTION f_jspl(jsonb)
RETURNS jsonb
TRANSFORM FOR TYPE jsonb
LANGUAGE plperl
AS $$
 my @arg = shift;
 $arg[0]->[0]->{rows}->[1]->{col_sec_json}->{data}->{class} = 'newval';
 $arg[0]->[0]->{rows}->[1]->{col_sec_json}->{to_date} = '2022-12-31';
 $arg[0]->[0]->{rows}->[1]->{col_sec_json}->{layout}->{fontSize} = 19;

 # to update all
 for my $r (@arg[0]) {
   for my $s (@$r) {
     $s->{rows}->[1]->{col_sec_json}->{data}->{class} = 'newval2';
     $s->{rows}->[1]->{col_sec_json}->{to_date} = '2023-01-01';
     $s->{rows}->[1]->{col_sec_json}->{layout}->{fontSize} = 18;
   }
 }


 return $arg[0];
$$ ;


update t_jsonb2 t set j =  f_jspl(t.j);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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