Json custom format - Forum - OpenEdge Development - Progress Community
 Forum

Json custom format

This question is answered

 
   "id":"Test01",
   "invoices":[  
      {  
         "advAmt":0,
         "amountTaxation":false,
         "currency":"INR",
         "customer":{  },
         "dateOfDelivery":"2019-02-06",
         "dueDate":"2019-02-06",
         "exemptedAmount":false,
         "grandTotal":100,
         "invoiceIssueDate":"2019-02-06",
         "invoiceNumber":"TEST_INV_MGF03_01",
         "invoiceReference":"",
         "invoiceType":"I",
         "lineItems":[  
            {  
               "amount":100,
               "amountAfterDiscount":100,
               "code":"A1",
               "descriptionArabic":"descarabic",
               "descriptionEnglish":"desceng",
               "discountAmount":0,
               "discountPercent":0,
               "price":100,
               "priceAfterVat":100,
               "quantity":1,
               "serialNumber":1,
               "totalVat":0,
               "uomArabic":"uomarabic",
               "uomEnglish":"uomeng",
               "vatPercent":0
            }
         ],
         "milestonePayments":false,
         "narrationArabic":"narraarabic",
         "narrationEnglish":"narraeng",
         "outstandingAmount":100,
         "percentOfCompletion":20,
         "profitMargin":{  },
         "remarksArabic":"remarkarabic",
         "remarksEnglish":"remarkeng",
         "salespersonArabic":"salesarabic",
         "salespersonEnglish":"saleseng",
         "selfAccountForTax":false,
         "shipmentRefNumber":"SHIP123",
         "supplier":{  },
         "totalBeforeVAT":100,
         "totalVAT":0
      }
   ],
   "supplierId":"663"
}

is it possible to write JSON file like this format if yes then how to define table schema or dataset relation to achive this using Write-json method.

Thanks in Adv.

Regards

-Shashank

Verified Answer
  • Hi Shashank

    I do not believe you can get there directly, but you can get close and then you can easily get the object you want:

    It looks like you have a dataset with some unidentified top level object, I will call it "order", then you have an "invoices" table with a DATA-RELATION ..... NESTED FOREIGN-KEY-HIDDEN  between the order and invoices, and then there is a table "lineItems" with a DATA-RELATION ..... NESTED FOREIGN-KEY-HIDDEN between invoices and lineItems. You need to define the dataset as SERIALIZE-HIDDEN.

    DEFINE TEMP-TABLE ttOrder SERIALIZE-NAME "order" .......
    
    DEFINE TEMP-TABLE ttInvoice SERIALIZE-NAME "invoices" .....
    
    DEFINE TEMP-TABLE ttLineItem SERIALIZE-NAME "lineItems" .....
    
    DEFINE DATASET dsOrderInvoices SERIALIZE-HIDDEN  
      FOR ttOrder, ttInvoice, ttLineItem
      DATA-RELATION invoiceOfOrder FOR ttOrder, ttInvoice
        RELATION-FIELDS(ordNo, ordNo) NESTED FOREIGN-KEY-HIDDEN
      DATA-RELATION lineOfInvoice FOR ttInvoice, ttLineItem
        RELATION-FIELDS(invNo, invNo) NESTED FOREIGN-KEY-HIDDEN.

    This will however serialize all the parent fields and then the child object array. If you want to control where it goes, you can define a PARENT-ID-RELATION, which allows you to specify which parent fields you want before and after the the child, but then you build the relation by putting the RECID of the parent in a field on the child.

    DEFINE TEMP-TABLE ttOrder SERIALIZE-NAME "order" .......
    
    DEFINE TEMP-TABLE ttInvoice SERIALIZE-NAME "invoices" 
       ...
       FIELD orderId as RECID
       ...
    
    DEFINE TEMP-TABLE ttLineItem SERIALIZE-NAME "lineItems" 
       ...
       FIELD invoiceId as RECID
       ...
    
    DEFINE DATASET dsOrderInvoices SERIALIZE-HIDDEN  
      FOR ttOrder, ttInvoice, ttLineItem
      PARENT-ID-RELATION invoiceOfOrder FOR ttOrder, ttInvoice
        PARENT-ID-FIELD (orderId)
        PARENT-FIELDS-BEFORE ("id")
        PARENT-FIELDS-AFTER("supplierId")
      PARENT-ID-RELATION lineOfInvoice FOR ttInvoice, ttLineItem
        PARENT-ID-FIELD (invoiceId)
        PARENT-FIELDS-BEFORE("advAmt", "amountTaxation", .... , "invoiceType")
        PARENT-FIELDS-AFTER("milestonePayments", ...... , "totalVat").

    Next, you serialize the dataset to a JsonObject. This object will have a single "order" node with an array of orders. The assumption is that there is always exactly one order. In that case, you can get the above in the long but clear way as follows.

    DEFINE VARIABLE orderdataset AS JsonObject NO-UNDO.
    DEFINE VARIABLE orders AS JsonArray NO-UNDO.
    DEFINE VARIABLE order AS JsonObject NO-UNDO.
    
    DATASET dsOrder:WRITE-JSON("JsonObject", orderdataset).
    
    orders = order:GetJsonArray("order").
    order = orders:GetJsonObject(1).
    

    You can do it even shorter like this:

    DEFINE VARIABLE order AS JsonObject NO-UNDO.
    
    DATASET dsOrder:WRITE-JSON("JsonObject", order).
    
    order = order:GetJsonArray("order"):GetJsonObject(1).
    

    Now the "order" variable contains the Json as you described.

    You can then user the "order" object's WRITE() method to get it into a LONG-CHAR, MEMPTR or CHARACTER. You can use the "order" object's WRITE-FILE()  to get it to disk.

    Simon L Prinsloo

    www.vidisolve.com

All Replies
  • Hi Shashank

    I do not believe you can get there directly, but you can get close and then you can easily get the object you want:

    It looks like you have a dataset with some unidentified top level object, I will call it "order", then you have an "invoices" table with a DATA-RELATION ..... NESTED FOREIGN-KEY-HIDDEN  between the order and invoices, and then there is a table "lineItems" with a DATA-RELATION ..... NESTED FOREIGN-KEY-HIDDEN between invoices and lineItems. You need to define the dataset as SERIALIZE-HIDDEN.

    DEFINE TEMP-TABLE ttOrder SERIALIZE-NAME "order" .......
    
    DEFINE TEMP-TABLE ttInvoice SERIALIZE-NAME "invoices" .....
    
    DEFINE TEMP-TABLE ttLineItem SERIALIZE-NAME "lineItems" .....
    
    DEFINE DATASET dsOrderInvoices SERIALIZE-HIDDEN  
      FOR ttOrder, ttInvoice, ttLineItem
      DATA-RELATION invoiceOfOrder FOR ttOrder, ttInvoice
        RELATION-FIELDS(ordNo, ordNo) NESTED FOREIGN-KEY-HIDDEN
      DATA-RELATION lineOfInvoice FOR ttInvoice, ttLineItem
        RELATION-FIELDS(invNo, invNo) NESTED FOREIGN-KEY-HIDDEN.

    This will however serialize all the parent fields and then the child object array. If you want to control where it goes, you can define a PARENT-ID-RELATION, which allows you to specify which parent fields you want before and after the the child, but then you build the relation by putting the RECID of the parent in a field on the child.

    DEFINE TEMP-TABLE ttOrder SERIALIZE-NAME "order" .......
    
    DEFINE TEMP-TABLE ttInvoice SERIALIZE-NAME "invoices" 
       ...
       FIELD orderId as RECID
       ...
    
    DEFINE TEMP-TABLE ttLineItem SERIALIZE-NAME "lineItems" 
       ...
       FIELD invoiceId as RECID
       ...
    
    DEFINE DATASET dsOrderInvoices SERIALIZE-HIDDEN  
      FOR ttOrder, ttInvoice, ttLineItem
      PARENT-ID-RELATION invoiceOfOrder FOR ttOrder, ttInvoice
        PARENT-ID-FIELD (orderId)
        PARENT-FIELDS-BEFORE ("id")
        PARENT-FIELDS-AFTER("supplierId")
      PARENT-ID-RELATION lineOfInvoice FOR ttInvoice, ttLineItem
        PARENT-ID-FIELD (invoiceId)
        PARENT-FIELDS-BEFORE("advAmt", "amountTaxation", .... , "invoiceType")
        PARENT-FIELDS-AFTER("milestonePayments", ...... , "totalVat").

    Next, you serialize the dataset to a JsonObject. This object will have a single "order" node with an array of orders. The assumption is that there is always exactly one order. In that case, you can get the above in the long but clear way as follows.

    DEFINE VARIABLE orderdataset AS JsonObject NO-UNDO.
    DEFINE VARIABLE orders AS JsonArray NO-UNDO.
    DEFINE VARIABLE order AS JsonObject NO-UNDO.
    
    DATASET dsOrder:WRITE-JSON("JsonObject", orderdataset).
    
    orders = order:GetJsonArray("order").
    order = orders:GetJsonObject(1).
    

    You can do it even shorter like this:

    DEFINE VARIABLE order AS JsonObject NO-UNDO.
    
    DATASET dsOrder:WRITE-JSON("JsonObject", order).
    
    order = order:GetJsonArray("order"):GetJsonObject(1).
    

    Now the "order" variable contains the Json as you described.

    You can then user the "order" object's WRITE() method to get it into a LONG-CHAR, MEMPTR or CHARACTER. You can use the "order" object's WRITE-FILE()  to get it to disk.

    Simon L Prinsloo

    www.vidisolve.com

  • Thanks a lot @Simon L Prinsloo definitely it will helpful for me..