Oracle Apex (PL/SQL) Loop through all JSON nested elements (Objects, Arrays, Strings, Numbers)

Question

I am using Oracle Apex 22.2 and Oracle Database XE 21c on CentOS 7. I want to be able to loop through all the nested elements in a JSON document/CLOB. I couldn't find any online functions/procedures which do that, so I wrote my own procedure. I followed the examples and guides from the following sources/links. However, I got a JSON syntax error. I checked my JSON data on two online JSON validators and both confirm that my JSON data is valid. Therefore, can you help figure out the problems. Also, any suggestions on improving my procedure or on a different solution to handle this would be appreciated.

Online Links/Guides

1 - https://asktom.oracle.com/pls/apex/asktom.search?tag=obtain-all-json-keys-from-json-columns

2 - How to read keys from nested JSON_OBJECT_T using JSON_KEY_LIST in PL /SQL?

3 - https://livesql.oracle.com/apex/livesql/file/content_GEZHJ12HQW7ACN66WST0HAKYN.html

JSON Validator Sites:

1 - https://jsonlint.com

2 - https://jsonformatter.curiousconcept.com

Error

    ERROR at line 1:
    ORA-40441: JSON syntax error
    ORA-06512: at "SYS.JDOM_T", line 16
    ORA-06512: at "SYS.JSON_ELEMENT_T", line 23
    ORA-06512: at "APEX.ETA_JSON_SERIALIZE", line 9
    ORA-06512: at "APEX.ETA_JSON_SERIALIZE", line 28
    ORA-06512: at "APEX.ETA_JSON_SERIALIZE", line 28
    ORA-06512: at "APEX.ETA_JSON_SERIALIZE", line 37
    ORA-06512: at "APEX.ETA_JSON_SERIALIZE", line 28
    ORA-06512: at line 86

My Procedure

create or replace PROCEDURE ETA_JSON_SERIALIZE (json_in IN CLOB, can_str IN OUT VARCHAR2)
IS
    l_element   JSON_ELEMENT_T;
    l_object    JSON_OBJECT_T;
    l_array     JSON_ARRAY_T;
    l_keys      JSON_KEY_LIST;
    l_str       VARCHAR2(32767);
BEGIN
    l_element := JSON_ELEMENT_T.parse( json_in );
    l_element.on_Error(1);
    FOR i IN 0 .. l_element.get_Size - 1 LOOP
        IF l_element.is_Scalar() THEN
            IF l_element.is_String() THEN
                l_str := l_str || 'String' || CHR(10);
            ELSIF l_element.is_Number() THEN
                l_str := l_str || 'Number' || CHR(10);
            ELSE
                l_str := l_str || 'Other Scalar' || CHR(10);
            END IF;
        ELSIF l_element.is_Object() THEN
            l_str := l_str || 'Object:' || CHR(10);
            l_object := JSON_OBJECT_T.parse( json_in );
            l_object.on_Error(1);
            l_keys := l_object.get_Keys();
            l_str := l_str || '     Element Size: ' || l_element.get_Size || CHR(10);
            l_str := l_str || '     Key Count: ' || l_keys.COUNT || CHR(10);
            FOR j IN l_keys.FIRST .. l_keys.LAST LOOP
                ETA_JSON_SERIALIZE( l_object.get( l_keys(j) ).to_Clob(), l_str );
            END LOOP;
        ELSIF l_element.is_Array() THEN
            l_str := l_str || 'Array:' || CHR(10);
            l_array := JSON_ARRAY_T.parse( json_in );
            l_array.on_Error(1);
            l_str := l_str || '     Element Size: ' || l_element.get_Size || CHR(10);
            l_str := l_str || '     Array Size: ' || l_array.get_Size || CHR(10);
            FOR j IN 0 .. l_array.get_Size - 1 LOOP
                ETA_JSON_SERIALIZE( l_array.get(j).to_Clob, l_str );
            END LOOP;
        ELSE
            l_str := l_str || 'Other Type' || CHR(10);
        END IF;
    END LOOP;
    can_str := l_str;
END;

PL/SQL Block

SET SERVEROUTPUT ON
DECLARE
   can_str  VARCHAR2(32767);
   l_doc    CLOB :=
   '{
   "documents": [{
      "issuer": {
         "type": "B",
         "id": "301188475",
         "name": "Hakim Misr Paco (POS-0)",
         "address": {
            "branchID": "-1",
            "country": "EG",
            "governate": "Cairo",
            "regionCity": "Nasr City",
            "street": "Mostafa El Nahas",
            "buildingNumber": "65"
         }
      },
      "receiver": {
         "type": "P",
         "id": " 29409200104255",
         "name": "Karim Ahmed Abdelhakim Hashem",
         "address": {
            "country": "EG",
            "governate": "Cairo",
            "regionCity": "Nasr City",
            "street": "Mostafa El Nahas",
            "buildingNumber": "65"
         }
      },
      "documentType": "I",
      "documentTypeVersion": "0.9",
      "dateTimeIssued": "2022-11-19T23:59:59Z",
      "taxpayerActivityCode": "2220",
      "internalID": "0-1",
      "invoiceLines": [{
         "description": "KFC_Test",
         "itemType": "GS1",
         "itemCode": "10006331",
         "unitType": "EA",
         "quantity": 1000,
         "unitValue": {
            "currencySold": "EGP",
            "amountEGP": 0.10000,
            "amountSold": 0
         },
         "salesTotal": 100,
         "total": 114,
         "valueDifference": 0,
         "totalTaxableFees": 0,
         "netTotal": 100,
         "itemsDiscount": 0,
         "discount": {
            "rate": 0,
            "amount": 0
         },
         "taxableItems": [{
            "taxType": "T1",
            "amount": 14,
            "subType": "V009",
            "rate": 14
         }],
         "internalCode": "Test_110"
      }],
      "totalSalesAmount": 100,
      "totalDiscountAmount": 0,
      "netAmount": 100,
      "taxTotals": [{
         "taxType": "T1",
         "amount": 14
      }],
      "extraDiscountAmount": 0,
      "totalItemsDiscountAmount": 0,
      "totalAmount": 114,
      "signatures": [{
         "signatureType": "I",
         "value": "NA"
      }]
   }]
}';
BEGIN 
   ETA_JSON_SERIALIZE(l_doc, can_str);
END;
/

Edit

I forgot to mention that the desired output should be similar to the one in this link

Original JSON

https://sdk.invoicing.eta.gov.eg/files/one-doc.json

Serialized JSON

https://sdk.invoicing.eta.gov.eg/files/one-doc-serialized.json.txt

I was able to fix the problem. This is my new updated code. But I am still trying to get the same output as in the Serialized JSON Link. Any suggestions/hints? Thank you.

Updated Code

create or replace PROCEDURE ETA_JSON_SERIALIZE (json_in IN JSON_ELEMENT_T, can_str IN OUT VARCHAR2)
IS
    l_object    JSON_OBJECT_T;
    l_array     JSON_ARRAY_T;
    l_keys      JSON_KEY_LIST;
    l_str       VARCHAR2(32767);
BEGIN
    FOR i IN 1 .. json_in.get_Size LOOP
        IF json_in.is_Scalar() THEN
            IF json_in.is_String() THEN
                l_str := '"' || json_in.to_String() || '" 1st Line Break' || CHR(10);
                DBMS_OUTPUT.PUT_LINE(l_str);
                RETURN;
            ELSIF json_in.is_Number() THEN
                l_str := '"' || json_in.to_Number() || '" 2nd Line Break' || CHR(10);
                DBMS_OUTPUT.PUT_LINE(l_str);
                RETURN;
            ELSE
                l_str := 'Other Scalar' || ' 3rd Line Break' || CHR(10);
                DBMS_OUTPUT.PUT_LINE(l_str);
            END IF;
        ELSIF json_in.is_Object() THEN
            -- l_str := 'Object:' || CHR(10);
            -- DBMS_OUTPUT.PUT_LINE(l_str);
            l_object := JSON_OBJECT_T( json_in );
            -- l_object.on_Error(1);
            l_keys := l_object.get_Keys();
            -- l_str := '     Element Size: ' || json_in.get_Size || CHR(10);
            -- l_str := '     Key Count: ' || l_keys.COUNT || CHR(10);
            -- DBMS_OUTPUT.PUT_LINE(l_str);
            l_str := UPPER(l_keys(i)) || ' 4th Line Break' || CHR(10);
            DBMS_OUTPUT.PUT_LINE(l_str);
            ETA_JSON_SERIALIZE( l_object.get( l_keys(i) ), l_str );
        ELSIF json_in.is_Array() THEN
            -- l_str := l_str || 'Array:' || CHR(10);
            -- DBMS_OUTPUT.PUT_LINE(l_str);
            l_array := JSON_ARRAY_T( json_in );
            -- l_array.on_Error(1);
            -- l_str := '     Element Size: ' || json_in.get_Size || CHR(10);
            -- DBMS_OUTPUT.PUT_LINE(l_str);
            -- l_str := '     Array Size: ' || l_array.get_Size || CHR(10);
            -- DBMS_OUTPUT.PUT_LINE(l_str);
            -- FOR j IN 0 .. l_array.get_Size - 1 LOOP
                ETA_JSON_SERIALIZE( l_array.get(i - 1), l_str );
            -- END LOOP;
        ELSE
            l_str := 'Other Type' || CHR(10)  || '5th Line Break' || CHR(10);
            DBMS_OUTPUT.PUT_LINE(l_str);
        END IF;
    END LOOP;
    can_str := l_str;
END;

Updated PL/SQL Block

SET SERVEROUTPUT ON
DECLARE
   can_str     VARCHAR2(32767);
   l_element   JSON_ELEMENT_T;
   l_element1  JSON_ELEMENT_T;
   l_object    JSON_OBJECT_T;
   l_keys      JSON_KEY_LIST;
   l_doc       CLOB := '{a:100, b:200, c:300}';
   l_doc1      CLOB := '{
   "department": "Accounting",
   "employees": [
   {
   "name": "Shelley,Higgins",
   "job": "Accounting Manager"
   },
   {
   "name": "William,Gietz",
   "job": "Public Accountant"
   }
   ]
   }';

   l_doc2      CLOB := '{
   "REQS": {
   "INDICATOR": "Y",
   "NUMBER": 0,
   "CATEGORY": "TU",
   "ID_R": 10888,
   "SUPPL_VAL": 0,
   "LINE_ITEMSSUB": {
   "QTY": 0,
   "TOTAL_QTY": 1,
   "PIPE": {
   "P_CODE": 9801,
   "P_ID": 7500030,
   "CC_CODE": "C6AJG4"
   }
   }
   },
   "Name": "Rajesh",
   "Age": 47
   }';
   l_doc3      CLOB := '{
   "documents": [{
      "issuer": {
         "type": "B",
         "id": "301188475",
         "name": "Hakim Misr Paco (POS-0)",
         "address": {
            "branchID": "-1",
            "country": "EG",
            "governate": "Cairo",
            "regionCity": "Nasr City",
            "street": "Mostafa El Nahas",
            "buildingNumber": "65"
         }
      },
      "receiver": {
         "type": "P",
         "id": " 29409200104255",
         "name": "Karim Ahmed Abdelhakim Hashem",
         "address": {
            "country": "EG",
            "governate": "Cairo",
            "regionCity": "Nasr City",
            "street": "Mostafa El Nahas",
            "buildingNumber": "65"
         }
      },
      "documentType": "I",
      "documentTypeVersion": "0.9",
      "dateTimeIssued": "2022-11-19T23:59:59Z",
      "taxpayerActivityCode": "2220",
      "internalID": "0-1",
      "invoiceLines": [{
         "description": "KFC_Test",
         "itemType": "GS1",
         "itemCode": "10006331",
         "unitType": "EA",
         "quantity": 1000,
         "unitValue": {
            "currencySold": "EGP",
            "amountEGP": 0.10000,
            "amountSold": 0
         },
         "salesTotal": 100,
         "total": 114,
         "valueDifference": 0,
         "totalTaxableFees": 0,
         "netTotal": 100,
         "itemsDiscount": 0,
         "discount": {
            "rate": 0,
            "amount": 0
         },
         "taxableItems": [{
            "taxType": "T1",
            "amount": 14,
            "subType": "V009",
            "rate": 14
         }],
         "internalCode": "Test_110"
      }],
      "totalSalesAmount": 100,
      "totalDiscountAmount": 0,
      "netAmount": 100,
      "taxTotals": [{
         "taxType": "T1",
         "amount": 14
      }],
      "extraDiscountAmount": 0,
      "totalItemsDiscountAmount": 0,
      "totalAmount": 114,
      "signatures": [{
         "signatureType": "I",
         "value": "NA"
      }]
   }]
}';

BEGIN 
   l_element := JSON_ELEMENT_T.parse( l_doc3 );
   ETA_JSON_SERIALIZE(l_element, can_str);
   DBMS_OUTPUT.PUT_LINE(can_str);
END;
/

You should not edit your question to be significantly different from the original. Also, you should not require people to click random links to see your expected output because we do not know where those links lead to and whether they may be harmful; all necessary information should be self-contained in the question. — MT0
– MT0, Commented Nov 22, 2022 at 11:18
I apologize for that. I didn't realize it that it was significantly different from the original. I will post another question with all the information self-contained. Thank you — kimoturbo
– kimoturbo, Commented Nov 22, 2022 at 11:42

MT0 · Accepted Answer · 2022-11-22 09:07:15Z

Your procedure is iterating through the object until it reaches the first scalar element "B" and it is passing that to be parsed as JSON, and that fails.

Instead, you need to check if the child is scalar when you loop through an object's keys or an array's elements and handle the scalar elements at that iteration:

create or replace PROCEDURE ETA_JSON_SERIALIZE (json_in IN CLOB, can_str IN OUT VARCHAR2)
IS
    l_element   JSON_ELEMENT_T;
    l_child     JSON_ELEMENT_T;
    l_object    JSON_OBJECT_T;
    l_array     JSON_ARRAY_T;
    l_keys      JSON_KEY_LIST;
    l_str       VARCHAR2(32767);
    FUNCTION parseScalar(
      p_element IN JSON_ELEMENT_T
    ) RETURN VARCHAR2
    IS
    BEGIN
      RETURN CASE
             WHEN p_element.is_String()
             THEN 'String'
             WHEN p_element.is_Number()
             THEN 'Number'
             ELSE 'Other Scalar'
             END || CHR(10);
      
    END;
BEGIN
    l_element := JSON_ELEMENT_T.parse( json_in );
    l_element.on_Error(1);
    FOR i IN 0 .. l_element.get_Size - 1 LOOP
        IF l_element.is_Scalar() THEN
            l_str := l_str || parseScalar(l_element);
        ELSIF l_element.is_Object() THEN
            l_str := l_str || 'Object:' || CHR(10);
            l_object := JSON_OBJECT_T.parse( json_in );
            l_object.on_Error(1);
            l_keys := l_object.get_Keys();
            l_str := l_str || '     Element Size: ' || l_element.get_Size || CHR(10);
            l_str := l_str || '     Key Count: ' || l_keys.COUNT || CHR(10);
            FOR j IN l_keys.FIRST .. l_keys.LAST LOOP
              l_child := l_object.get( l_keys(j) );
              IF l_child.is_Scalar() THEN
                l_str := l_str || parseScalar(l_child);
              ELSE
                ETA_JSON_SERIALIZE( l_child.to_Clob(), l_str );
              END IF;
            END LOOP;
        ELSIF l_element.is_Array() THEN
            l_str := l_str || 'Array:' || CHR(10);
            l_array := JSON_ARRAY_T.parse( json_in );
            l_array.on_Error(1);
            l_str := l_str || '     Element Size: ' || l_element.get_Size || CHR(10);
            l_str := l_str || '     Array Size: ' || l_array.get_Size || CHR(10);
            FOR j IN 0 .. l_array.get_Size - 1 LOOP
                l_child := l_array.get(j);
                IF l_child.is_Scalar() THEN
                  l_str := l_str || parseScalar(l_child);
                ELSE
                  ETA_JSON_SERIALIZE( l_child.to_Clob(), l_str );
                END IF;
            END LOOP;
        ELSE
            l_str := l_str || 'Other Type' || CHR(10);
        END IF;
    END LOOP;
    can_str := l_str;
END;
/

fiddle

@kimoturbo No, because I answered your original question. If you have follow-up/different questions then you can always ask a new question; you should not significantly change the original question.

Collectives™ on Stack Overflow

Oracle Apex (PL/SQL) Loop through all JSON nested elements (Objects, Arrays, Strings, Numbers)

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related