How to parse JSON array data in Oracle APEX

Question

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;

Olafur Tryggvason · Accepted Answer

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:

How to parse JSON array data in Oracle APEX

Answers (2)

Related Questions