I am using Oracle Apex 22.2 and Oracle Database XE 21c on CentOS 7. I want to loop through all the nested elements in a JSON document/CLOB and achieve the Canonical String (shown below). Also, any suggestions on improving my procedure or on a different solution to handle this would be appreciated. I wrote my own procedure shown below following the algorithm shown below. All help and suggestions are really appreciated.
Algorithm Pseudo Code
function string Serialize(documentStructure)
if documentStructure is simple value type
return """ documentStructure.value """
end if
var serializedString = ""
foreach element in the structure:
if element is not array type
serializeString.Append (""" element.name.uppercase """)
serializeString.Append ( Serialize(element.value) )
end if
if element is of array type
serializeString.Append (""" element.name.uppercase """)
foreach array element in element:
// use below line for JSON because subelements of array in JSON do not have own names
serializeString.Append (""" element.name.uppercase """)
serializeString.Append ( Serialize(arrayelement.value) )
end foreach
end if
end foreach
return serializedString
end function
Procedure 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;
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;
/
JSON Format
<pre>
{
"issuer": {
"address": {
"branchID": "1",
"country": "EG",
"governate": "Cairo",
"regionCity": "Nasr City",
"street": "580 Clementina Key",
"buildingNumber": "Bldg. 0",
"postalCode": "68030",
"floor": "1",
"room": "123",
"landmark": "7660 Melody Trail",
"additionalInformation": "beside Townhall"
},
"type": "B",
"id": "113317713",
"name": "Issuer Company"
},
"receiver": {
"address": {
"country": "EG",
"governate": "Egypt",
"regionCity": "Mufazat al Ismlyah",
"street": "580 Clementina Key",
"buildingNumber": "Bldg. 0",
"postalCode": "68030",
"floor": "1",
"room": "123",
"landmark": "7660 Melody Trail",
"additionalInformation": "beside Townhall"
},
"type": "B",
"id": "313717919",
"name": "Receiver"
},
"documentType": "I",
"documentTypeVersion": "0.9",
"dateTimeIssued": "2020-10-27T23:59:59Z",
"taxpayerActivityCode": "4620",
"internalID": "IID1",
"purchaseOrderReference": "P-233-A6375",
"purchaseOrderDescription": "purchase Order description",
"salesOrderReference": "1231",
"salesOrderDescription": "Sales Order description",
"proformaInvoiceNumber": "SomeValue",
"payment": {
"bankName": "SomeValue",
"bankAddress": "SomeValue",
"bankAccountNo": "SomeValue",
"bankAccountIBAN": "",
"swiftCode": "",
"terms": "SomeValue"
},
"delivery": {
"approach": "SomeValue",
"packaging": "SomeValue",
"dateValidity": "2020-09-28T09:30:10Z",
"exportPort": "SomeValue",
"countryOfOrigin": "EG",
"grossWeight": 10.50,
"netWeight": 20.50,
"terms": "SomeValue"
},
"invoiceLines": [
{
"description": "Computer1",
"itemType": "GPC",
"itemCode": "10001774",
"unitType": "EA",
"quantity": 5,
"internalCode": "IC0",
"salesTotal": 947.00,
"total": 2969.89,
"valueDifference": 7.00,
"totalTaxableFees": 817.42,
"netTotal": 880.71,
"itemsDiscount": 5.00,
"unitValue": {
"currencySold": "EUR",
"amountEGP": 189.40,
"amountSold": 10.00,
"currencyExchangeRate": 18.94
},
"discount": {
"rate": 7,
"amount": 66.29
},
"taxableItems": [
{
"taxType": "T1",
"amount": 272.07,
"subType": "T1",
"rate": 14.00
},
{
"taxType": "T2",
"amount": 208.22,
"subType": "T2",
"rate": 12
},
{
"taxType": "T3",
"amount": 30.00,
"subType": "T3",
"rate": 0.00
},
{
"taxType": "T4",
"amount": 43.79,
"subType": "T4",
"rate": 5.00
},
{
"taxType": "T5",
"amount": 123.30,
"subType": "T5",
"rate": 14.00
},
{
"taxType": "T6",
"amount": 60.00,
"subType": "T6",
"rate": 0.00
},
{
"taxType": "T7",
"amount": 88.07,
"subType": "T7",
"rate": 10.00
},
{
"taxType": "T8",
"amount": 123.30,
"subType": "T8",
"rate": 14.00
},
{
"taxType": "T9",
"amount": 105.69,
"subType": "T9",
"rate": 12.00
},
{
"taxType": "T10",
"amount": 88.07,
"subType": "T10",
"rate": 10.00
},
{
"taxType": "T11",
"amount": 123.30,
"subType": "T11",
"rate": 14.00
},
{
"taxType": "T12",
"amount": 105.69,
"subType": "T12",
"rate": 12.00
},
{
"taxType": "T13",
"amount": 88.07,
"subType": "T13",
"rate": 10.00
},
{
"taxType": "T14",
"amount": 123.30,
"subType": "T14",
"rate": 14.00
},
{
"taxType": "T15",
"amount": 105.69,
"subType": "T15",
"rate": 12.00
},
{
"taxType": "T16",
"amount": 88.07,
"subType": "T16",
"rate": 10.00
},
{
"taxType": "T17",
"amount": 88.07,
"subType": "T17",
"rate": 10.00
},
{
"taxType": "T18",
"amount": 123.30,
"subType": "T18",
"rate": 14.00
},
{
"taxType": "T19",
"amount": 105.69,
"subType": "T19",
"rate": 12.00
},
{
"taxType": "T20",
"amount": 88.07,
"subType": "T20",
"rate": 10.00
}
]
},
{
"description": "Computer2",
"itemType": "GPC",
"itemCode": "10003752",
"unitType": "EA",
"quantity": 7,
"internalCode": "IC0",
"salesTotal": 662.90,
"total": 2226.61,
"valueDifference": 6.00,
"totalTaxableFees": 621.51,
"netTotal": 652.90,
"itemsDiscount": 9.00,
"unitValue": {
"currencySold": "EUR",
"amountEGP": 94.70,
"amountSold": 5.00,
"currencyExchangeRate": 18.94
},
"discount": {
"rate": 0,
"amount": 10.00
},
"taxableItems": [
{
"taxType": "T1",
"amount": 205.47,
"subType": "T1",
"rate": 14.00
},
{
"taxType": "T2",
"amount": 157.25,
"subType": "T2",
"rate": 12
},
{
"taxType": "T3",
"amount": 30.00,
"subType": "T3",
"rate": 0.00
},
{
"taxType": "T4",
"amount": 32.20,
"subType": "T4",
"rate": 5.00
},
{
"taxType": "T5",
"amount": 91.41,
"subType": "T5",
"rate": 14.00
},
{
"taxType": "T6",
"amount": 60.00,
"subType": "T6",
"rate": 0.00
},
{
"taxType": "T7",
"amount": 65.29,
"subType": "T7",
"rate": 10.00
},
{
"taxType": "T8",
"amount": 91.41,
"subType": "T8",
"rate": 14.00
},
{
"taxType": "T9",
"amount": 78.35,
"subType": "T9",
"rate": 12.00
},
{
"taxType": "T10",
"amount": 65.29,
"subType": "T10",
"rate": 10.00
},
{
"taxType": "T11",
"amount": 91.41,
"subType": "T11",
"rate": 14.00
},
{
"taxType": "T12",
"amount": 78.35,
"subType": "T12",
"rate": 12.00
},
{
"taxType": "T13",
"amount": 65.29,
"subType": "T13",
"rate": 10.00
},
{
"taxType": "T14",
"amount": 91.41,
"subType": "T14",
"rate": 14.00
},
{
"taxType": "T15",
"amount": 78.35,
"subType": "T15",
"rate": 12.00
},
{
"taxType": "T16",
"amount": 65.29,
"subType": "T16",
"rate": 10.00
},
{
"taxType": "T17",
"amount": 65.29,
"subType": "T17",
"rate": 10.00
},
{
"taxType": "T18",
"amount": 91.41,
"subType": "T18",
"rate": 14.00
},
{
"taxType": "T19",
"amount": 78.35,
"subType": "T19",
"rate": 12.00
},
{
"taxType": "T20",
"amount": 65.29,
"subType": "T20",
"rate": 10.00
}
]
}
],
"totalDiscountAmount": 76.29,
"totalSalesAmount": 1609.90,
"netAmount": 1533.61,
"taxTotals": [
{
"taxType": "T1",
"amount": 477.54
},
{
"taxType": "T2",
"amount": 365.47
},
{
"taxType": "T3",
"amount": 60.00
},
{
"taxType": "T4",
"amount": 75.99
},
{
"taxType": "T5",
"amount": 214.71
},
{
"taxType": "T6",
"amount": 120.00
},
{
"taxType": "T7",
"amount": 153.36
},
{
"taxType": "T8",
"amount": 214.71
},
{
"taxType": "T9",
"amount": 184.04
},
{
"taxType": "T10",
"amount": 153.36
},
{
"taxType": "T11",
"amount": 214.71
},
{
"taxType": "T12",
"amount": 184.04
},
{
"taxType": "T13",
"amount": 153.36
},
{
"taxType": "T14",
"amount": 214.71
},
{
"taxType": "T15",
"amount": 184.04
},
{
"taxType": "T16",
"amount": 153.36
},
{
"taxType": "T17",
"amount": 153.36
},
{
"taxType": "T18",
"amount": 214.71
},
{
"taxType": "T19",
"amount": 184.04
},
{
"taxType": "T20",
"amount": 153.36
}
],
"totalAmount": 5191.50,
"extraDiscountAmount": 5.00,
"totalItemsDiscountAmount": 14.00
}
Canonical String
"ISSUER""ADDRESS""BRANCHID""1""COUNTRY""EG""GOVERNATE""Cairo""REGIONCITY""Nasr City""STREET""580 Clementina Key""BUILDINGNUMBER""Bldg. 0""POSTALCODE""68030""FLOOR""1""ROOM""123""LANDMARK""7660 Melody Trail""ADDITIONALINFORMATION""beside Townhall""TYPE""B""ID""113317713""NAME""Issuer Company""RECEIVER""ADDRESS""COUNTRY""EG""GOVERNATE""Egypt""REGIONCITY""Mufazat al Ismlyah""STREET""580 Clementina Key""BUILDINGNUMBER""Bldg. 0""POSTALCODE""68030""FLOOR""1""ROOM""123""LANDMARK""7660 Melody Trail""ADDITIONALINFORMATION""beside Townhall""TYPE""B""ID""313717919""NAME""Receiver""DOCUMENTTYPE""I""DOCUMENTTYPEVERSION""0.9""DATETIMEISSUED""2020-10-27T23:59:59Z""TAXPAYERACTIVITYCODE""4620""INTERNALID""IID1""PURCHASEORDERREFERENCE""P-233-A6375""PURCHASEORDERDESCRIPTION""purchase Order description""SALESORDERREFERENCE""1231""SALESORDERDESCRIPTION""Sales Order description""PROFORMAINVOICENUMBER""SomeValue""PAYMENT""BANKNAME""SomeValue""BANKADDRESS""SomeValue""BANKACCOUNTNO""SomeValue""BANKACCOUNTIBAN""""SWIFTCODE""""TERMS""SomeValue""DELIVERY""APPROACH""SomeValue""PACKAGING""SomeValue""DATEVALIDITY""2020-09-28T09:30:10Z""EXPORTPORT""SomeValue""COUNTRYOFORIGIN""EG""GROSSWEIGHT""10.50""NETWEIGHT""20.50""TERMS""SomeValue""INVOICELINES""INVOICELINES""DESCRIPTION""Computer1""ITEMTYPE""GPC""ITEMCODE""10001774""UNITTYPE""EA""QUANTITY""5""INTERNALCODE""IC0""SALESTOTAL""947.00""TOTAL""2969.89""VALUEDIFFERENCE""7.00""TOTALTAXABLEFEES""817.42""NETTOTAL""880.71""ITEMSDISCOUNT""5.00""UNITVALUE""CURRENCYSOLD""EUR""AMOUNTEGP""189.40""AMOUNTSOLD""10.00""CURRENCYEXCHANGERATE""18.94""DISCOUNT""RATE""7""AMOUNT""66.29""TAXABLEITEMS""TAXABLEITEMS""TAXTYPE""T1""AMOUNT""272.07""SUBTYPE""T1""RATE""14.00""TAXABLEITEMS""TAXTYPE""T2""AMOUNT""208.22""SUBTYPE""T2""RATE""12""TAXABLEITEMS""TAXTYPE""T3""AMOUNT""30.00""SUBTYPE""T3""RATE""0.00""TAXABLEITEMS""TAXTYPE""T4""AMOUNT""43.79""SUBTYPE""T4""RATE""5.00""TAXABLEITEMS""TAXTYPE""T5""AMOUNT""123.30""SUBTYPE""T5""RATE""14.00""TAXABLEITEMS""TAXTYPE""T6""AMOUNT""60.00""SUBTYPE""T6""RATE""0.00""TAXABLEITEMS""TAXTYPE""T7""AMOUNT""88.07""SUBTYPE""T7""RATE""10.00""TAXABLEITEMS""TAXTYPE""T8""AMOUNT""123.30""SUBTYPE""T8""RATE""14.00""TAXABLEITEMS""TAXTYPE""T9""AMOUNT""105.69""SUBTYPE""T9""RATE""12.00""TAXABLEITEMS""TAXTYPE""T10""AMOUNT""88.07""SUBTYPE""T10""RATE""10.00""TAXABLEITEMS""TAXTYPE""T11""AMOUNT""123.30""SUBTYPE""T11""RATE""14.00""TAXABLEITEMS""TAXTYPE""T12""AMOUNT""105.69""SUBTYPE""T12""RATE""12.00""TAXABLEITEMS""TAXTYPE""T13""AMOUNT""88.07""SUBTYPE""T13""RATE""10.00""TAXABLEITEMS""TAXTYPE""T14""AMOUNT""123.30""SUBTYPE""T14""RATE""14.00""TAXABLEITEMS""TAXTYPE""T15""AMOUNT""105.69""SUBTYPE""T15""RATE""12.00""TAXABLEITEMS""TAXTYPE""T16""AMOUNT""88.07""SUBTYPE""T16""RATE""10.00""TAXABLEITEMS""TAXTYPE""T17""AMOUNT""88.07""SUBTYPE""T17""RATE""10.00""TAXABLEITEMS""TAXTYPE""T18""AMOUNT""123.30""SUBTYPE""T18""RATE""14.00""TAXABLEITEMS""TAXTYPE""T19""AMOUNT""105.69""SUBTYPE""T19""RATE""12.00""TAXABLEITEMS""TAXTYPE""T20""AMOUNT""88.07""SUBTYPE""T20""RATE""10.00""INVOICELINES""DESCRIPTION""Computer2""ITEMTYPE""GPC""ITEMCODE""10003752""UNITTYPE""EA""QUANTITY""7""INTERNALCODE""IC0""SALESTOTAL""662.90""TOTAL""2226.61""VALUEDIFFERENCE""6.00""TOTALTAXABLEFEES""621.51""NETTOTAL""652.90""ITEMSDISCOUNT""9.00""UNITVALUE""CURRENCYSOLD""EUR""AMOUNTEGP""94.70""AMOUNTSOLD""5.00""CURRENCYEXCHANGERATE""18.94""DISCOUNT""RATE""0""AMOUNT""10.00""TAXABLEITEMS""TAXABLEITEMS""TAXTYPE""T1""AMOUNT""205.47""SUBTYPE""T1""RATE""14.00""TAXABLEITEMS""TAXTYPE""T2""AMOUNT""157.25""SUBTYPE""T2""RATE""12""TAXABLEITEMS""TAXTYPE""T3""AMOUNT""30.00""SUBTYPE""T3""RATE""0.00""TAXABLEITEMS""TAXTYPE""T4""AMOUNT""32.20""SUBTYPE""T4""RATE""5.00""TAXABLEITEMS""TAXTYPE""T5""AMOUNT""91.41""SUBTYPE""T5""RATE""14.00""TAXABLEITEMS""TAXTYPE""T6""AMOUNT""60.00""SUBTYPE""T6""RATE""0.00""TAXABLEITEMS""TAXTYPE""T7""AMOUNT""65.29""SUBTYPE""T7""RATE""10.00""TAXABLEITEMS""TAXTYPE""T8""AMOUNT""91.41""SUBTYPE""T8""RATE""14.00""TAXABLEITEMS""TAXTYPE""T9""AMOUNT""78.35""SUBTYPE""T9""RATE""12.00""TAXABLEITEMS""TAXTYPE""T10""AMOUNT""65.29""SUBTYPE""T10""RATE""10.00""TAXABLEITEMS""TAXTYPE""T11""AMOUNT""91.41""SUBTYPE""T11""RATE""14.00""TAXABLEITEMS""TAXTYPE""T12""AMOUNT""78.35""SUBTYPE""T12""RATE""12.00""TAXABLEITEMS""TAXTYPE""T13""AMOUNT""65.29""SUBTYPE""T13""RATE""10.00""TAXABLEITEMS""TAXTYPE""T14""AMOUNT""91.41""SUBTYPE""T14""RATE""14.00""TAXABLEITEMS""TAXTYPE""T15""AMOUNT""78.35""SUBTYPE""T15""RATE""12.00""TAXABLEITEMS""TAXTYPE""T16""AMOUNT""65.29""SUBTYPE""T16""RATE""10.00""TAXABLEITEMS""TAXTYPE""T17""AMOUNT""65.29""SUBTYPE""T17""RATE""10.00""TAXABLEITEMS""TAXTYPE""T18""AMOUNT""91.41""SUBTYPE""T18""RATE""14.00""TAXABLEITEMS""TAXTYPE""T19""AMOUNT""78.35""SUBTYPE""T19""RATE""12.00""TAXABLEITEMS""TAXTYPE""T20""AMOUNT""65.29""SUBTYPE""T20""RATE""10.00""TOTALDISCOUNTAMOUNT""76.29""TOTALSALESAMOUNT""1609.90""NETAMOUNT""1533.61""TAXTOTALS""TAXTOTALS""TAXTYPE""T1""AMOUNT""477.54""TAXTOTALS""TAXTYPE""T2""AMOUNT""365.47""TAXTOTALS""TAXTYPE""T3""AMOUNT""60.00""TAXTOTALS""TAXTYPE""T4""AMOUNT""75.99""TAXTOTALS""TAXTYPE""T5""AMOUNT""214.71""TAXTOTALS""TAXTYPE""T6""AMOUNT""120.00""TAXTOTALS""TAXTYPE""T7""AMOUNT""153.36""TAXTOTALS""TAXTYPE""T8""AMOUNT""214.71""TAXTOTALS""TAXTYPE""T9""AMOUNT""184.04""TAXTOTALS""TAXTYPE""T10""AMOUNT""153.36""TAXTOTALS""TAXTYPE""T11""AMOUNT""214.71""TAXTOTALS""TAXTYPE""T12""AMOUNT""184.04""TAXTOTALS""TAXTYPE""T13""AMOUNT""153.36""TAXTOTALS""TAXTYPE""T14""AMOUNT""214.71""TAXTOTALS""TAXTYPE""T15""AMOUNT""184.04""TAXTOTALS""TAXTYPE""T16""AMOUNT""153.36""TAXTOTALS""TAXTYPE""T17""AMOUNT""153.36""TAXTOTALS""TAXTYPE""T18""AMOUNT""214.71""TAXTOTALS""TAXTYPE""T19""AMOUNT""184.04""TAXTOTALS""TAXTYPE""T20""AMOUNT""153.36""TOTALAMOUNT""5191.50""EXTRADISCOUNTAMOUNT""5.00""TOTALITEMSDISCOUNTAMOUNT""14.00"
CodePudding user response：
You can use:
create or replace PROCEDURE ETA_JSON_SERIALIZE (
json_in IN JSON_ELEMENT_T,
can_str IN OUT VARCHAR2,
object_key IN VARCHAR2 DEFAULT NULL
)
IS
BEGIN
IF json_in.is_Object() THEN
DECLARE
l_object JSON_OBJECT_T := TREAT(json_in AS JSON_OBJECT_T);
l_keys JSON_KEY_LIST := l_object.get_Keys();
BEGIN
FOR i IN 1 .. l_keys.COUNT LOOP
can_str := can_str || '"'||UPPER(l_keys(i))||'"';
ETA_JSON_SERIALIZE(l_object.get(l_keys(i)), can_str, l_keys(i));
END LOOP;
END;
ELSIF json_in.is_Array() THEN
DECLARE
l_array JSON_ARRAY_T := TREAT(json_in AS JSON_ARRAY_T);
BEGIN
FOR i IN 0 .. l_array.get_size -1 LOOP
IF i > 0 AND object_key IS NOT NULL THEN
can_str := can_str || '"'||UPPER(object_key)||'"';
END IF;
ETA_JSON_SERIALIZE(l_array.get(i), can_str);
END LOOP;
END;
ELSIF json_in.is_Scalar() THEN
can_str := can_str || json_in.to_String();
END IF;
END;
/
Which, for the sample data:
DECLARE
can_str VARCHAR2(32767);
l_element JSON_ELEMENT_T;
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(JSON_ELEMENT_T.parse( l_doc ), can_str);
DBMS_OUTPUT.PUT_LINE(can_str);
END;
/
Outputs:
"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"