I have following JSON output.I need to parse the data in to a table .Please help me the code.
{
"type": "Campaign",
"currentStatus": "Active",
"id": "206",
"createdAt": "1488438112",
"createdBy": "370",
"depth": "complete",
"folderId": "1428",
"name": "Car Loan",
"elements": [
{
"type": "CampaignAddToProgramBuilderAction",
"id": "1197",
"name": "Create Lead",
"memberCount": "0",
},
}
],
"isReadOnly": "false",
"runAsUserId": "372",
"actualCost": "2500.00",
"budgetedCost": "0.00",
"campaignCategory": "contact",
"campaignType": "GB",
"crmId": "",
"endAt": "1496289599",
"fieldValues": [
{
"type": "FieldValue",
"id": "8",
"value": "test"
},
{
"type": "FieldValue",
"id": "9",
"value": "APAC"
},
{
"type": "FieldValue",
"id": "11",
"value": ""
},
{
"type": "FieldValue",
"id": "12",
"value": "Direct Mail Campaigns"
},
{
"type": "FieldValue",
"id": "13",
"value": ""
}
],
"firstActivation": "1488439250",
"isEmailMarketingCampaign": "false",
"isIncludedInROI": "true",
}
I have to load the all the fields in to a table.following code is loading the data without nested fields,Please help to add "actual Cost" and field values(type,id,value)in below code.
declare
l_ws_response_clob CLOB;
l_ws_url VARCHAR2(500) := 'your URL';--above given the out put of JSON
l_list json_list;
l_obj json;
l_col1 VARCHAR2(100);
l_col2 VARCHAR2(100);
l_col3 VARCHAR2(100);
l_col4 VARCHAR2(100);
l_col5 VARCHAR2(100);
l_col6 VARCHAR2(100);
l_col7 VARCHAR2(100);
l_col8 VARCHAR2(100);
begin
--get JSON
apex_web_service.g_request_headers(1).name := 'Accept';
apex_web_service.g_request_headers(1).value := 'application/json; charset=utf-8';
apex_web_service.g_request_headers(2).name := 'Content-Type';
apex_web_service.g_request_headers(2).value := 'application/json; charset=utf-8';
l_ws_response_clob := apex_web_service.make_rest_request(
p_url => l_ws_url,
p_username => 'TEST',
p_password => 'TEST',
p_http_method => 'GET'
);
l_obj := json(l_ws_response_clob);
l_list := json_list(l_obj.get('elements'));
for i in 1..l_list.count LOOP
l_col1 := json_ext.get_string(json(l_list.get(i)),'type');
l_col2 := json_ext.get_string(json(l_list.get(i)),'currentStatus');
l_col3 := json_ext.get_string(json(l_list.get(i)),'folderId');
l_col4 := json_ext.get_string(json(l_list.get(i)),'name');
l_col5 := json_ext.get_string(json(l_list.get(i)),'id');
l_col6 := json_ext.get_string(json(l_list.get(i)),'createdAt');
l_col7 := json_ext.get_string(json(l_list.get(i)),'createdBy');
l_col8 := json_ext.get_string(json(l_list.get(i)),'isEmailMarketingCampaign');
--Actual cost and field values(type,id,value) needs to be added here which are in array list.Please help code here
INSERT INTO CAMPAIGN_TEST(RECORD_NUM,TYPE,CURRENT_STATUS,FOLDERID,NAME,ID,CREATEDAT,CREATEDBY,ISEMAILMARKETINGCAMPAIGN,) VALUES (i,l_col1,l_col2,l_col3,l_col4,l_col5,l_col6,l_col7,l_col8);
end LOOP;
end;
That's a pretty bad JSON you are dealing with there. Dangling commas (not allowed) and numbers stored as text instead of being delivered as numbers.
But that aside this is how you parse the data
declare
l_json varchar2 (32767) := '{"type": "Campaign","currentStatus": "Active","id": "206","createdAt": "1488438112","createdBy": "370",
"depth": "complete","folderId": "1428","name": "Car Loan", "elements": [ {
"type": "CampaignAddToProgramBuilderAction",
"id": "1197",
"name": "Create Lead",
"memberCount": "0"
}],
"isReadOnly": "false","runAsUserId": "372","actualCost": "2500.00","budgetedCost": "0.00","campaignCategory": "contact","campaignType": "GB",
"crmId": "","endAt": "1496289599","fieldValues": [ { "type": "FieldValue", "id": "8", "value": "test" },
{ "type": "FieldValue", "id": "9", "value": "APAC" },
{ "type": "FieldValue", "id": "11", "value": "" },
{ "type": "FieldValue", "id": "12", "value": "Direct Mail Campaigns" },
{ "type": "FieldValue", "id": "13", "value": "" }
],
"firstActivation": "1488439250","isEmailMarketingCampaign": "false","isIncludedInROI": "true"}';
l_number number;
begin
apex_json.parse (l_json);
--actualCost
l_number := to_number (apex_json.get_varchar2 ('actualCost'), '999999999990D00', 'NLS_NUMERIC_CHARACTERS=''.,''');
dbms_output.put_line ('Actual cost: ' || l_number);
-- fieldValues
for i in 1 .. apex_json.get_count ('fieldValues') loop
dbms_output.put_line ('Item number ' || i);
dbms_output.put_line (chr (9) || ' * Type: ' || apex_json.get_varchar2 ('fieldValues[%d].type', i));
dbms_output.put_line (chr (9) || ' * Id: ' || apex_json.get_varchar2 ('fieldValues[%d].id', i));
dbms_output.put_line (chr (9) || ' * Value: ' || apex_json.get_varchar2 ('fieldValues[%d].value', i));
end loop;
end;
Which then gives the output of:
Actual cost: 2500
Item number 1
* Type: FieldValue
* Id: 8
* Value: test
Item number 2
* Type: FieldValue
* Id: 9
* Value: APAC
Item number 3
* Type: FieldValue
* Id: 11
* Value:
Item number 4
* Type: FieldValue
* Id: 12
* Value: Direct Mail Campaigns
Item number 5
* Type: FieldValue
* Id: 13
* Value: