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