DEVHIDE
Login Or Sign up

How to parse JSON array data in Oracle APEX

9.6k Views Asked by At

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;
json oracle-apex pljson
Original Q&A
2

There are 2 best solutions below

0
Olafur Tryggvason On BEST 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:
0
Bahadirs On

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;

Related Questions in JSON

Related Questions in ORACLE-APEX

Related Questions in PLJSON

Trending Questions

Popular # Hahtags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs

Popular Questions

.

Copyright © 2021 Jogjafile Inc.