PL/SQL JSON manipulation

Viewed 51

I have a json with following structure having two arrays.

"tieredContribution": [
    
  ],
  "contributions": [
    {
      "employeePercentage": 23.0,
      "employeeLimit": null,
      "spousePercentage": null,
    },
    
  ]
}

I have wriiten a PL/SQL to update contribution column in the following json. The code for that is following

 CREATE OR REPLACE PROCEDURE JSON_ADDITION AS 
BEGIN
  declare

je JSON_ELEMENT_T;
jo JSON_OBJECT_T;
j_add JSON_OBJECT_T;
li_arr JSON_ARRAY_T;
V_json clob;

begin

for j in (select esmd.id, esmd.value, pc.code
from enrollment_setup_metadata esmd
join enrollment_setup es on esmd.ENROLLMENT_SETUP_ID = es.id
join enrollment_setup_segment setup_segment on (es.id=setup_segment.enrollment_setup_id)
join product_category_wise_detail pcwd on (setup_segment.id= pcwd.enrl_setup_sgmt_id )
join product_category pc on (pc.id=pcwd.product_category_id)
where pc.code = 'VOLUNTARYSTD'
and esmd.key = 'VIRTUAL_SEGMENT'
and esmd.id = '1508817'
)
loop

jo := JSON_OBJECT_T.parse(j.value);
DBMS_OUTPUT.put_line (jo.stringify ());
 
        li_arr := jo.get_Array('contributions');   
         
      j_add := JSON_OBJECT_T('{
      "employeePercentage": 0.0,
      "employeeLimit": null,
      "spousePercentage": null,
      "spouseLimit": null,
      "dependentPercentage": null,
      "dependentLimit": null,
      "maximumDependentsAllowed": null,
      "applicantOverallLimit": null,
      "familyPercentage": null,
      "familyLimit": null,
      "productCategoryCode": "VOLUNTARYSTD",
      "premiumFrequency": null,
      "contributionType": "DOLLAR_OR_PERCENTAGE",
      "transitLimit": null,
      "parkingLimit": null,
      "participationStrategyOption": null,
      "participationRatio": null,
      "participationRatioRequired": null,
      "terminationDate": null,
      "newHireWaitingPeriod": null,
      "newHireWaitingPeriodCustomValue": null,
      "active": false,
      "dependentContributionAvailable": false,
      "productCategoryShared": false,
      "terminationExhausted": false,
      "activeRequested": false,
      "terminationRequested": false,
      "dependentContribution": "",
      "employeeContribution": "0%"
    }');   
    li_arr.append(j_add);
    
    
      
end loop;
DBMS_OUTPUT.put_line (li_arr.stringify ());
V_json := jo.stringify();

end;  
END JSON_ADDITION;

I need to update jo object where both arrays are stored with the updated contributions array value. I am unable to decide how to update original array object with updated values for contributions in such a way structure of original json is maintained

0 Answers
Related