I am trying to get nested field's values from json which is returned by function apex_web_service.make_rest_request.

DECLARE
v_address_json clob;
v_address_response clob;
l_object_address   json_object_t;
BEGIN
          SELECT
        json_object ('Addresses' value json_object(
        'AddName' value  ('HQ') ,     
        'AddLine1' value tab1.ADDRESS_LINE1 ,
        .
        .
        .
        .
    into 
    v_address_json
         
      FROM
          tab1 t1,
          tab2 t2 
          WHERE
          .....
          .....;

    v_address_response := apex_web_service.make_rest_request
                    (
                        p_url           => 'https://...../addresses',
                        p_http_method   => 'POST',
                        p_body          => v_address_json
    );
   

    DBMS_OUTPUT.PUT_LINE('v_address_response : '||v_address_response);

At this point I am getting below in v_address_response.

{
  "Metadata" : {
    "application" : "",
    "applicationRefId" : ""
  },
  "APIResponse" : {
    "Status" : "SUCCESS",
    "Error" : {
      "Code" : "",
      "Message" : "",
      "Detail" : ""
    }
  },
  "ven" : {
    "Id" : 12345,
    "Addresses" : [ {
      "vAddId" : 1122334455,
      "vAddName" : "HQ",
      "AddLine1" : "1/A2, ABC, XYZ ROAD, IN",
      "City" : "JKL",
      "State" : "AB",
      "PCode" : "102030",
      "Country" : "IN",
      "TaxReg" : [ {
        "RegId" : 998877,
        "EffectiveFrom" : "2029-11-13"  
      } ],
      "TaxRep" : [ {
        "TaxRepId" : 665544,
        "EffectiveFrom" : "2022-01-01"
        
      } ]
    } ]
  }
}

further I am trying to get field's value as below.

 l_object_address := json_object_t.parse(v_address_response);

        
        if l_object_address.get_Object('APIResponse').get_string('Status') = 'SUCCESS'
            then
                        
            DBMS_OUTPUT.PUT_LINE(' New Address ID : '||l_object_address.get_Object('Addresses').get_string('vAddId')); --output xx

            else
            DBMS_OUTPUT.PUT_LINE('Error in creating address'); 
        end if;

exception when others then 
null;    
end;

in the above section I am able to get value for APIResponse--> Status i.e 'SUCCESS' but unable to get value of vAddId, RegId and other nested fields. At comment, output xx, nothing is getting printed.

CodePudding user response：

You need to descend into the ven object first and then Addresses is an array of objects, and not an object, so you need to find the zero-indexed element of the array and then get the attribute of the object. To do that, you can use:

DECLARE
  v_address_response CLOB := '{
  "Metadata" : {
    "application" : "",
    "applicationRefId" : ""
  },
  "APIResponse" : {
    "Status" : "SUCCESS",
    "Error" : {
      "Code" : "",
      "Message" : "",
      "Detail" : ""
    }
  },
  "ven" : {
    "Id" : 12345,
    "Addresses" : [ {
      "vAddId" : 1122334455,
      "vAddName" : "HQ",
      "AddLine1" : "1/A2, ABC, XYZ ROAD, IN",
      "City" : "JKL",
      "State" : "AB",
      "PCode" : "102030",
      "Country" : "IN",
      "TaxReg" : [ {
        "RegId" : 998877,
        "EffectiveFrom" : "2029-11-13"  
      } ],
      "TaxRep" : [ {
        "TaxRepId" : 665544,
        "EffectiveFrom" : "2022-01-01"
        
      } ]
    } ]
  }
}';
  l_object JSON_OBJECT_T;
  l_address JSON_OBJECT_T;
BEGIN
  l_object := json_object_t.parse(v_address_response);
  if l_object.get_Object('APIResponse').get_string('Status') = 'SUCCESS' then
    l_address := TREAT( l_object.get_Object('ven').get_Array('Addresses').get(0) AS JSON_OBJECT_T );
    DBMS_OUTPUT.PUT_LINE(' New Address ID : '||l_address.get_Number('vAddId')); --output xx
  else
    DBMS_OUTPUT.PUT_LINE('Error in creating address'); 
  end if;
end;
/

Which outputs:

New Address ID : 1122334455

fiddle

CodePudding user response：

If you're just after reading values out of the JSON (and not intend to traverse the "object tree" up and down") I would use the JSON_TABLE SQL Function, as follows:

BEGIN
  for i in (
    select status,
           vAddId,
           vAddName,
           RegId,
           EffectiveFrom
           -- , other columns go here
      from json_table(
               v_address_response,
               '$'
               columns(
                   status varchar2(255) path '$.APIResponse.Status',
                   -- other columns on this level go here
                   ven_id number        path '$.ven.Id',
                   nested path '$.ven.Addresses' columns(
                       vAddId    number        path '$.vAddId',
                       vAddName  varchar2(255) path '$.vAddName',
                       -- other columns on this level go here
                       nested path '$.TaxReg' columns(
                           RegId         number        path '$.RegId',
                           EffectiveFrom varchar2(255) path '$.EffectiveFrom'
                       )
                   )
               )
            )
  )
  loop
    -- process the data here
    if i.status = 'SUCCESS' then
      DBMS_OUTPUT.PUT_LINE(' New Address ID : '|| i.vAddId);
    else
      DBMS_OUTPUT.PUT_LINE('Error in creating address'); 
    end if;
  end loop;
end;
/

fiddle