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;

Please let me know the code how to parse inner array data – Arjun Mar 15 '17 at 13:31 — Arjun, Mar 15 '17 at 13:31

score 3 · Accepted Answer · answered Mar 21 '17 at 10:40

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:

score 0 · Answer 2 · answered Mar 21 '17 at 14:47

you may base your code with following code instead of harcoded json you can send l_obj to xmltable function then you can use insert to table

 select T1.*,T2.*
from xmltable (
        '/json'
        passing apex_json.to_xmltype('
{
    "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"
}

')
        columns
           type varchar2(1000) path '/row/type',
           currentStatus varchar2(1000) path '/row/currentStatus',
           folderId varchar2(1000) path '/row/folderId',
           name varchar2(1000) path '/row/name',
           id varchar2(1000) path '/row/id',
           createdAt varchar2(1000) path '/row/createdAt',
           createdBy varchar2(1000) path '/row/createdBy',
           isEmailMarketingCampaign varchar2(1000) path '/row/isEmailMarketingCampaign',
           actualCost varchar2(1000) path '/row/actualCost' ) T1,

xmltable (
        '/json/fieldValues/row'
        passing apex_json.to_xmltype('
{
    "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"
}

')
        columns
           type varchar2(1000) path '/row/type',
           id varchar2(1000) path '/row/id',
           value varchar2(1000) path '/row/value'
           ) T2;

How to parse JSON array data in Oracle APEX

2 Answers2