Problem with reading JSON file in OpenEdge Progress 10.2B

The json file must be in a format that can be loaded into temp-tables in order to use read-json(). A temp-table can have multiple records and needs to be defined as a json array. The address in this json is an object and cannot be loaded into a temp-table. The description field is also not in the right place.

The ABL has full JSON support that allow you to read any valid json file in case the format cannot be changed.  

So you means to say that the file is having wrong format. But I am getting the proper response when I will send this json file to the server. There is no way around to read that file with this format only.

The format cannot be loaded into a dataset with read-json. If you need to keep this format then you will need to read the json using the ABL JSON objects.  This seems to exist also in 10.2B:

documentation.progress.com/.../dvjsn.pdf  

You probably still need to find a way to restructure the address info if you want to manage it in a dataset though.  

Thanks Havard.....!!

I have referred this documents 'Working with Json' as well but there is also no such techniques available which will read this kind of json file. Because while defining prodataset and temp-tables we have to define the schema of those temp-tables and dataset before reading the json.....so we must know the actual structure of the json file which we are going to read.....this make me to write a specific program for a one type of json file.

So my Question here is that. Do we have any techniques or ways through which I can read the json file dynamically?....means single program works for all types of json file.

If you have any documents or links related with json which will tell me more about json with Progress 4gl in detail. Please share with us.

Sorry, it seems the ABL JSON objects I referred to were added in version 11. It is documented in "Working with JSON"  in the newer versions.

The JSON objects allow you to parse any JSON file and get a JSON Object that you then can traverse with code. You can then use the names and values retrieved and access ABL temp-tables and buffers dynamically.  Or you can use the buffer name and field names (serialize-name) to retrieve data from the JSON  object.  I'm sure you could use the JSON info to create dynamic temp-tables and datasets, but I believe you are better off defining the dataset and temp-tables statically (non dynamic) and keep them in separate classes (or procedures). It is difficult to write business logic with 100% dynamic datasets and temp-tables. It may also be difficult to write common generic logic that  figures out how to store JSON objects that cannot be stored as-is in a temp-table, like the address in your case. The majority of the code could still be in a common base class that uses dynamic access for general logic.

ok.....so is there any way to figure out the difference between the json object and json array while reading or writing the json file because in my example address is a json object and pieces and sites are json arrays so if I will take the address as a child temp-table of sites then while writing or reading this kind of json it will treat the address as json array and expecting "[" (big brackets) for this.....which is wrong.

Do you have any idea how to treat with these two json object and json array separately?

Yes, there is a GetType method on the JsonObject and JsonArray that you can use to check the type of the child node before you read it. Note that this was added very recently, possibly as late as 11.5. Before that you had to try one type and catch and check errors and try the next type until you succeeded...  When you write json you new  a JsonObject or JsonArray depending on what ABL construct you are exporting.      

I am having this code now in this code I want address should be treated as Object without "[" while writing. How should I do it

DEFINE VARIABLE cTargetType AS CHARACTER NO-UNDO.

DEFINE VARIABLE cFile       AS CHARACTER NO-UNDO.

DEFINE VARIABLE lFormatted  AS LOGICAL   NO-UNDO.

DEFINE VARIABLE lRetOK      AS LOGICAL   NO-UNDO.

DEFINE VARIABLE i AS INTEGER     NO-UNDO.

DEFINE VARIABLE j AS INTEGER     NO-UNDO.

DEFINE VARIABLE wv-request AS longchar     NO-UNDO.

def temp-table job no-undo

 field jobno         as char SERIALIZE-HIDDEN

 field description   as char

 field allow_marketplace as char

 index idx-job is primary jobno.

DEFINE TEMP-TABLE sites NO-UNDO

 field site_type     as char

 field jobno         as char SERIALIZE-HIDDEN

 field seq-no        as int SERIALIZE-HIDDEN

 field contact_name  as char

 field contact_phone as CHAR

 field after_text    as char

 field pieces        as char

 index idx-site is primary seq-no.

DEFINE TEMP-TABLE address NO-UNDO

 field seq-no        as int SERIALIZE-HIDDEN

 field street       as char

 field street2      as char

 field city as char

 field state as char

 field postal_code as char

 field country as char

 index idx_addr is primary seq-no

 /*index primary idx-address field site-type */

 .

DEFINE TEMP-TABLE pieces NO-UNDO

 field seq-no        as int SERIALIZE-HIDDEN

 /*field pieces        as char*/

 field package_type_name  as char

 field number        as char

 field weight        as char

 index idx_pieces is primary seq-no

 .

define dataset dsCustomer for job, sites, address, pieces

 data-relation jobsites     for job, sites relation-field(jobno,jobno) nested

 data-relation sitesaddress for sites, address relation-fields(seq-no,seq-no) nested /*FOREIGN-KEY-HIDDEN*/

 data-relation sitespieces  for sites, pieces relation-fields(seq-no,seq-no) nested.

create job.

assign job.jobno       = "0122314"

      job.description = "web order 0122314"

      job.allow_marketplace = "TRUE"

      .

do i = 1 to 2:

 create sites.

 assign sites.site_type      = (if i = 1 then "PICKUP" else "DROPOFF")

        sites.seq-no         = i

        sites.jobno          = "0122314"

        sites.contact_name   = (if i = 1 then "Precision" else "James Bond")

        sites.contact_phone  = (if i = 1 then "+1-312-3345678" else "+1-312-9999999")

        sites.after_text     = (if i = 1 then "2015-01-14T15:00:00" else "")

        .

 create address.

 assign address.seq-no      = i

        address.street      = (if i = 1 then "307 7th Ave" else "211 Central Park W")                

        address.street2     = ""

        address.city        = "NEW YORK"  

        address.state       = "NY"

        address.postal_code = (if i = 1 then "10001" else "10024")

        address.country     = "US"

        .

 if i = 2 then

 do:

   create pieces.

   assign pieces.seq-no            = i

          pieces.package_type_name = "box"

          pieces.number            = "1"

          pieces.weight            = "31"

          .

 end.

end.

/*dataset dsCustomer:fill().*/

ASSIGN    

 cTargetType = "file"  

 cFile       = "H:\job-request.json"  

 lFormatted  = TRUE.

/*TEMP-TABLE ttCust:WRITE-JSON(cTargetType, cFile, lFormatted).*/

DATASET dsCustomer:WRITE-JSON(cTargetType, cFile, lFormatted, ?, YES).

copy-lob from file cFile to wv-request.

wv-request = substr(wv-request,index(wv-request,":") + 2).

wv-request = substr(wv-request,1,r-index(wv-request,"}") - 1).

copy-lob wv-request to file cFile.

/*-------- output  looks like this --------------- */

{

     "description": "web order 0122314",

     "allow_marketplace": "TRUE",

     "sites": [

       {

         "site_type": "PICKUP",

         "contact_name": "Precision",

         "contact_phone": "+1-312-3345678",

         "after_text": "2015-01-14T15:00:00",

         "address": [        <------------------------------  these brackets I don't want                

           {

             "street": "307 7th Ave",

             "city": "NEW YORK",

             "state": "NY",

             "postal_code": "10001",

             "country": "US"

           }

         ] <------------------------------  these brackets I don't want

       },

       {

         "site_type": "DROPOFF",

         "contact_name": "James Bond",

         "contact_phone": "+1-312-9999999",

         "address": [ <------------------------------  these brackets I don't want

           {

             "street": "211 Central Park W",

             "city": "NEW YORK",

             "state": "NY",

             "postal_code": "10024",

             "country": "US"

           }

         ], <------------------------------  these brackets I don't want

         "pieces": [

           {

             "package_type_name": "box",

             "number": "1",

             "weight": "31"

           }

         ]

       }

     ]

   }

Note: I have to use Progress 10.2B only

Thanks for the assistance............Havard.