Problem with reading JSON file in OpenEdge Progress 10.2B - Forum - Progress User Groups - Progress Community

Problem with reading JSON file in OpenEdge Progress 10.2B

 Forum

Problem with reading JSON file in OpenEdge Progress 10.2B

  • Hi Everyone,

    I am struggling while reading json file in Open Edge Progress version 10.2B using read-json() method.

    I have the JSON file like this

    {
      "description": "Web Order 1092703",
      "sites": [
        {
          "site_type": "PICKUP",
          "address": {
            "street": "240 East Houston",
            "street2": "Apt 13E",
            "city": "New York",
            "state": "NY",
            "postal_code": "10002",
            "country": "US"
          },
          "contact_name": "Emma (front desk)",
          "contact_phone": "212-555-5500",
          "after_text": "2015-01-12T17:30:00"
        },
        {
          "site_type": "DROPOFF",
          "address": {
            "street": "67 Lexington Ave",
            "city": "New York",
            "state": "NY",
            "postal_code": "10010",
            "country": "US"
          },
          "contact_name": "Jon Q Public",
          "contact_phone": "212-555-1022",
          "pieces": [
            {
              "package_type_name": "box",
              "number": 1,
              "weight": 3
            }
          ]
        }
      ]
    }

    Can anyone please help me out is there any better way reading this json file into ProDataset or temp-table.
    Note: I am able to read the file if there is a common filed is available in the file between nested blocks of data like given below

    {"dsCustomer": {
    "ttCust": [
    {
    "tt-key": "key1",
    "tt-value": "value1",
    "tt-seq": "seq1",
    "tt-order": "ord1",
    "ttOrd": [
    {
    "tt-order": "ord1",
    "tt-pkg": "pkg11"
    },
    {
    "tt-order": "ord1",
    "tt-pkg": "pkg12"
    }
    ]
    }
    ]
    }
    }

    Here tt-order is the field worked as common field so I can read this file easily using read-json() method but when we doesn't have this type of data then I have problem.

    I have referred the "Working with JSON" as well there also it is not properly mention how to deal with this type of json file.

    Any suggestion is highly applicable.

  • 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

  • There is no easy way to  do this in 10.2B. 
     
    You would either have to write the entire json by hand  or use write-json (to a longchar probably) and remove the undesired brackets from the json after.
     
     
    From: ankitshukla [mailto:bounce-ankitshukla@community.progress.com]
    Sent: Thursday, January 15, 2015 11:06 AM
    To: PUG-Forum@community.progress.com
    Subject: RE: [Progress User Groups - Forum] Problem with reading JSON file in OpenEdge Progress 10.2B
     
    Reply by ankitshukla

    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"

               }

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