Forums >> Programming >> Proof of Concept (POC)

Jump to:




bvstone

Parsing JSON with RPG and JSONTOOL

Posted:

Parsing JSON with RPG and JSONTOOL

Note: JSONTOOL has been updated.  Please see the reply to this post for the updated functions.

This example is to show how to parse JSON data in RPG using our JSONTOOL product.

It is mainly to explain what syntax is needed since there are nested objects in JSON, and those objects can also be Arrays.

Testing and syntax validation can also be done with our JSONTOOL parsing example web page.  There is also a great online JSON formatting and validation site here.

The following example data will be used.  It is sample data from a transport company that is used to get information on the status of delivery vehicles.

{  
   "type":"PagedVehicleResult",
   "index":0,
   "limit":100,
   "count":6,
   "total":6,
   "vehicle":[  
      {  
         "id":352104,
         "vin":"123456",
         "label":"3070",
         "color":"WHITE",
         "make":"VOLVO",
         "model":"VN",
         "deviceSerialNumber":"5010395849",
         "year":2005,
         "odometer":{  
            "units":"MILES",
            "timestamp":"2014-10-14T18:37:13Z",
            "readOnly":"TRUE",
            "value":199400.38
         },
         "engineRunTime":{  
            "isTracked":"ALWAYS",
            "timestamp":"2014-10-14T18:33:49Z",
            "readOnly":"TRUE",
            "value":"P478DT6H24M0.000S"
         },
         "licensePlate":{  
            "state":"FL",
            "country":"US"
         },
         "trackableItemType":"VEHICLE",
         "fuelType":"DIESEL",
         "createdTimestamp":{  
            "readOnly":true,
            "value":"2013-11-15T16:43:47Z"
         },
         "modifiedTimestamp":{  
            "readOnly":true,
            "value":"2014-10-10T19:11:50Z"
         }
      },
      {  
         "id":354261,
         "vin":"234567",
         "label":"3292",
         "color":"WHITE",
         "make":"VOLVO",
         "model":"VN",
         "deviceSerialNumber":"5010411594",
         "year":2011,
         "odometer":{  
            "units":"MILES",
            "timestamp":"2014-10-14T18:38:14Z",
            "readOnly":"TRUE",
            "value":366985.99
         },
         "engineRunTime":{  
            "isTracked":"ALWAYS",
            "timestamp":"2014-10-14T18:18:11Z",
            "readOnly":"TRUE",
            "value":"P299DT19H0M0.000S"
         },
         "licensePlate":{  
            "state":"FL",
            "country":"US"
         },
         "trackableItemType":"VEHICLE",
         "fuelType":"DIESEL",
         "createdTimestamp":{  
            "readOnly":true,
            "value":"2013-11-22T18:30:51Z"
         },
         "modifiedTimestamp":{  
            "readOnly":true,
            "value":"2014-10-10T15:22:25Z"
         }
      },
      {  
         "id":356045,
         "vin":"345678",
         "label":"401",
         "color":"WHITE",
         "make":"MACK",
         "model":"CH",
         "deviceSerialNumber":"5010551993",
         "year":2001,
         "odometer":{  
            "units":"MILES",
            "timestamp":"2014-06-03T16:56:49Z",
            "readOnly":"TRUE",
            "value":203119.47
         },
         "engineRunTime":{  
            "isTracked":"ALWAYS",
            "timestamp":"2014-06-03T15:52:39Z",
            "readOnly":"TRUE",
            "value":"P229DT1H24M0.000S"
         },
         "licensePlate":{  
            "state":"FL",
            "country":"US"
         },
         "trackableItemType":"VEHICLE",
         "fuelType":"DIESEL",
         "createdTimestamp":{  
            "readOnly":true,
            "value":"2013-12-02T18:19:00Z"
         },
         "modifiedTimestamp":{  
            "readOnly":true,
            "value":"2014-10-10T17:11:55Z"
         }
      },
      {  
         "id":352049,
         "vin":"456789",
         "label":"3035",
         "color":"WHITE",
         "make":"VOVLO",
         "model":"VN430",
         "deviceSerialNumber":"5010404824",
         "year":2006,
         "odometer":{  
            "units":"MILES",
            "timestamp":"2014-10-14T18:38:11Z",
            "readOnly":"TRUE",
            "value":347126.47
         },
         "engineRunTime":{  
            "isTracked":"ALWAYS",
            "timestamp":"2014-10-14T18:13:48Z",
            "readOnly":"TRUE",
            "value":"P623DT6H12M0.000S"
         },
         "licensePlate":{  
            "state":"FL",
            "country":"US",
            "value":"084NLI"
         },
         "trackableItemType":"VEHICLE",
         "fuelType":"DIESEL",
         "createdTimestamp":{  
            "readOnly":true,
            "value":"2013-11-15T15:21:15Z"
         },
         "modifiedTimestamp":{  
            "readOnly":true,
            "value":"2013-11-15T15:21:15Z"
         }
      },
      {  
         "id":359172,
         "vin":"567890",
         "label":"3281",
         "color":"WHITE",
         "make":"VOLVO",
         "model":"VN",
         "deviceSerialNumber":"5010555410",
         "year":2011,
         "odometer":{  
            "units":"MILES",
            "timestamp":"2014-10-13T14:58:33Z",
            "readOnly":"TRUE",
            "value":360237.9
         },
         "engineRunTime":{  
            "isTracked":"ALWAYS",
            "timestamp":"2014-10-13T12:14:37Z",
            "readOnly":"TRUE",
            "value":"P316DT7H24M0.000S"
         },
         "licensePlate":{  
            "state":"FL",
            "country":"US"
         },
         "trackableItemType":"VEHICLE",
         "fuelType":"DIESEL",
         "createdTimestamp":{  
            "readOnly":true,
            "value":"2013-12-19T16:15:48Z"
         },
         "modifiedTimestamp":{  
            "readOnly":true,
            "value":"2014-10-10T15:21:15Z"
         }
      },
      {  
         "id":361056,
         "vin":"678901",
         "label":"3272",
         "color":"WHITE",
         "make":"WHITE",
         "model":"VN",
         "deviceSerialNumber":"5010587534",
         "year":2011,
         "odometer":{  
            "units":"MILES",
            "timestamp":"2014-10-10T19:27:39Z",
            "readOnly":"TRUE",
            "value":528542.0900000001
         },
         "engineRunTime":{  
            "isTracked":"ALWAYS",
            "timestamp":"2014-10-10T19:25:20Z",
            "readOnly":"TRUE",
            "value":"P300DT20H12M0.000S"
         },
         "licensePlate":{  
            "state":"FL",
            "country":"US"
         },
         "trackableItemType":"VEHICLE",
         "fuelType":"DIESEL",
         "createdTimestamp":{  
            "readOnly":true,
            "value":"2013-12-31T13:14:03Z"
         },
         "modifiedTimestamp":{  
            "readOnly":true,
            "value":"2014-10-10T15:18:36Z"
         }
      }
   ]
}

The first thing we see here is that the JSON data returns the vehicle information "objects" as an array.  Arrays are denoted using the [ and ] characters.  So, the vehicle array looks like this:

"vehicle":[ ..... ]

Inside the vehicle array is information about each vehicle.  If we were to strip out the first vehicle in the array, it could stand alone as it's own JSON object and would look like the following:

      {  
         "id":352104,
         "vin":"123456",
         "label":"3070",
         "color":"WHITE",
         "make":"VOLVO",
         "model":"VN",
         "deviceSerialNumber":"5010395849",
         "year":2005,
         "odometer":{  
            "units":"MILES",
            "timestamp":"2014-10-14T18:37:13Z",
            "readOnly":"TRUE",
            "value":199400.38
         },
         "engineRunTime":{  
            "isTracked":"ALWAYS",
            "timestamp":"2014-10-14T18:33:49Z",
            "readOnly":"TRUE",
            "value":"P478DT6H24M0.000S"
         },
         "licensePlate":{  
            "state":"FL",
            "country":"US"
         },
         "trackableItemType":"VEHICLE",
         "fuelType":"DIESEL",
         "createdTimestamp":{  
            "readOnly":true,
            "value":"2013-11-15T16:43:47Z"
         },
         "modifiedTimestamp":{  
            "readOnly":true,
            "value":"2014-10-10T19:11:50Z"
         }
      }

 

In order to read through and parse the data it's good to know the total number of vehicles in the array.  This JSON data just happens to provide that information in an object named "count" (or "total" would also provide that number).  In the following examples, we will assume that the variable JSONData contains our JSON data.

First, we need to retrieve that count.  That is done using the following RPG code:

count = #st_CtoN(#js_GetData(JSONData:'count')); 

Now, because we want the data returned as numeric, we are wrapping value returned in one of our home-grown subprocedures that converts character to numeric.  If we were to split these up to make it more readable, it would look like the following:

data = #js_GetData(JSONData:'count'); 
count = #st_CtoN(data); 

Because the data returned from the #js_GetData always returns character data, we need to then convert that to a numeric value to use in our loop when reading data.

Once we have the number of vehicles returned we can then enter a loop to read the information for each of them.  In this example we'll strip out the id, vin number, odometer value and license plate state.

for i = 1 to count;                                        
  vElem = 'vehicle[' + %char(i) + ']';                     
  vElemData = vElem + ':id';                               
  v_id(i) = #js_GetData(JSONData:vElemData);               
  vElemData = vElem + ':vin';                              
  v_vin(i) = #js_GetData(JSONData:vElemData);              
  vElemData = vElem + ':odometer:value';                   
  v_odo(i) = #st_CtoN(#js_GetData(JSONData:vElemData));    
  vElemData = vElem + ':licensePlate:state';               
  v_lpstate(i) = #js_GetData(JSONData:vElemData);          
endfor;                                                    

The first thing we're doing is creating a string that contains the vehicle array element to retrieve.  For each loop, the value for vElem will be:

  1. vehicle[1]
  2. vehicle[2]
  3. vehicle[3]
    .....

To this, we add on the object we want to retrieve.  So, for the first vehicle's ID, the identifier we will use to pass to the #js_GetData() subprocedure would be:

vehicle[1]:id

When we get to the odometer value and the license plate state, things are a little different since those JSON objects contain more nested data.  All we need to do to retrieve this information is again, provide the proper "path" to the object data we want.

For the first vehicle's odometer value the the identifier we will use is:

vehicle[1]:odometer:value

So, the main point here to notice is that array objects are defined using name[n] where n is the element number we want to retrieve and each path is separated by a colon.  It should also be noted that the identifier names ARE case sensitive.

The entire source for the program is as follows:

     H DFTACTGRP(*NO) BNDDIR('BVSTOOLS')
      ****************************************************************
      * Imports
      ****************************************************************
      /COPY QCOPYSRC,P.IFSSTD
      /COPY QCOPYSRC,P.JSON
      /COPY QCOPYSRC,P.STRINGS
      ****************************************************************
     D MAX_VEHICLES    C                   CONST(999)
      *
     D JSONData        S          65535    Varying
     D vElem           S          65535    Varying
     D vElemData       S          65535    Varying
     D fd              S             10i 0
     D count           S             10i 0
     D i               S             10i 0
      *
     D v_id            S            128    DIM(MAX_VEHICLES)
     D v_vin           S            128    DIM(MAX_VEHICLES)
     D v_odo           S             13P 5 DIM(MAX_VEHICLES)
     D v_lpstate       S              2    DIM(MAX_VEHICLES)
      ********************************************************************
      /free

       fd = #openStmf('/bvstools/jsondata.json');
       #getStmf(fd:JSONData);
       #closeStmf(fd);

       count = #st_CtoN(#js_GetData(JSONData:'count'));

       for i = 1 to count;
         vElem = 'vehicle[' + %char(i) + ']';
         vElemData = vElem + ':id';
         v_id(i) = #js_GetData(JSONData:vElemData);
         vElemData = vElem + ':vin';
         v_vin(i) = #js_GetData(JSONData:vElemData);
         vElemData = vElem + ':odometer:value';
         v_odo(i) = #st_CtoN(#js_GetData(JSONData:vElemData));
         vElemData = vElem + ':licensePlate:state';
         v_lpstate(i) = #js_GetData(JSONData:vElemData);
       endfor;

       *INLR = *ON;

      /end-free

We are  using some home-grown subprocedures in this program.  The first is used to open a stream file in the IFS and load a variable with the JSON data from it.  The other is our #st_CtoN() which converts text from character to numeric in a little nicer fashion that if we would use the %dec() BIF.  We've been using ours for years and it does exactly what we want, so we're sticking with it.  :)

 


Last edited 12/11/2014 at 17:57:22



Latest Posts:

How to Whitelist GreenTools for G Suite (G4G) For Your Organization How to Whitelist GreenTools for G Suite (G4G) For Your Organization
Posted by November 5, 2023
BVSTools >> BVSTools Software Discussion >> GreenTools for G Suite (Google Apps) (G4G) Specific Discussion
QuickBooks Online Releases QuickBooks Online Releases "New Invoices!"... and It's Terrible!
Posted by October 8, 2023
QuickBooks >> QuickBooks Online
Admin/4i - What is it? Admin/4i - What is it?
Posted by September 30, 2023
Vendor Corner >> MSD Information Technology
BVSTools Releases Send Job Log to BVSTools (SNDLOG2BVS) Command BVSTools Releases Send Job Log to BVSTools (SNDLOG2BVS) Command
Posted by August 28, 2023
BVSTools >> BVSTools Announcements
MAILTOOL Now Allows Email Redirection for Development and Testing MAILTOOL Now Allows Email Redirection for Development and Testing
Posted by May 27, 2023
BVSTools >> BVSTools Announcements >> eMail Tool (MAILTOOL) Specific Announcements
GreenTools for Microsoft Apps (G4MS) Now Supports Footers When Sending Email GreenTools for Microsoft Apps (G4MS) Now Supports Footers When Sending Email
Posted by March 29, 2023
BVSTools >> BVSTools Announcements >> GreenTools for Microsoft Apps (G4MS) Specific Announcements
QuickBooks Online - Subtotals and Discounts Frustration QuickBooks Online - Subtotals and Discounts Frustration
Posted by March 16, 2023
QuickBooks >> QuickBooks Online
Making the Switch From QuickBooks Desktop to QuickBooks Online - No Picnic Making the Switch From QuickBooks Desktop to QuickBooks Online - No Picnic
Posted by March 16, 2023
QuickBooks >> QuickBooks Online
BVSTools Software Verified on V7R5 and Power10 BVSTools Software Verified on V7R5 and Power10
Posted by December 9, 2022
BVSTools >> BVSTools Announcements
Microsoft Office 365 Servers and Random Errors Issue Microsoft Office 365 Servers and Random Errors Issue
Posted by November 14, 2022
BVSTools >> BVSTools Software Discussion >> Email Tools (MAILTOOL) Specific Discussion
Sending/Resending Emails Using a MIME File with MAILTOOL Sending/Resending Emails Using a MIME File with MAILTOOL
Posted by November 8, 2022
BVSTools >> BVSTools Software Discussion >> Email Tools (MAILTOOL) Specific Discussion
Sending an HTML Email on Your IBM i Using MAILTOOL Sending an HTML Email on Your IBM i Using MAILTOOL
Posted by November 1, 2022
BVSTools >> BVSTools Software Discussion >> Email Tools (MAILTOOL) Specific Discussion
Transferring License Keys from One System to Another Transferring License Keys from One System to Another
Posted by October 31, 2022
BVSTools >> BVSTools Software Discussion
Calculating the Size of a File Before Base64 Encoding Calculating the Size of a File Before Base64 Encoding
Posted by August 13, 2022
Programming >> RPG Programming
GreenTools for Microsoft Apps (G4MS) v9.12 Now Includes Function to Send Emails using MIME File GreenTools for Microsoft Apps (G4MS) v9.12 Now Includes Function to Send Emails using MIME File
Posted by August 11, 2022
BVSTools >> BVSTools Announcements >> GreenTools for Microsoft Apps (G4MS) Specific Announcements
bvstone

RE: Parsing JSON with RPG and JSONTOOL (using F.JSON2)

Posted:

RE: Parsing JSON with RPG and JSONTOOL (using F.JSON2)

Recently we updated our JSONTOOL package to include a new service program for parsing JSON data.  The name is F.JSON2.  The main differences between this and the original F.JSON service program (which is still included in the JSONTOOL package) are:

  • 64k limit is now 16 meg
  • The ability to parse data from a stream file "directly"
  • The init() and cleanup() procedures

Assuming the data is the same as the previous post, if we were to update our application to use the new F.JSON2 service program it would look like the following:

     H DFTACTGRP(*NO) BNDDIR('BVSTOOLS')
      ****************************************************************
      * Imports
      ****************************************************************
      /COPY QCOPYSRC,P.IFSSTD
      /COPY QCOPYSRC,P.JSON2
      /COPY QCOPYSRC,P.STRINGS
      ****************************************************************
     D MAX_VEHICLES    C                   CONST(999)
      *
     D vElem           S          65535    Varying
     D vElemData       S          65535    Varying
     D errMsg          S            256
     D rc              S             10i 0
     D count           S             10i 0
     D i               S             10i 0
      *
     D v_id            S            128    DIM(MAX_VEHICLES)
     D v_vin           S            128    DIM(MAX_VEHICLES)
     D v_odo           S             13P 5 DIM(MAX_VEHICLES)
     D v_lpstate       S              2    DIM(MAX_VEHICLES)
      ********************************************************************
      /free

       #js2_init();
       #js2_setValue('stmf':'/tmp/jsontestfiles/jsondata.json');
       rc = #js2_loadStmf(errMsg);

       if (rc > 0);
         #js2_setValue('tag':'count');
         count = #st_CtoN(#js2_getDataStr(errMsg));

         for i = 1 to count;
           vElem = 'vehicle[' + %char(i) + ']';
           vElemData = vElem + ':id';
           #js2_setValue('tag':vElemData);
           v_id(i) = #js2_getDataStr(errMsg);

           vElemData = vElem + ':vin';
           #js2_setValue('tag':vElemData);
           v_vin(i) = #js2_getDataStr(errMsg);

           vElemData = vElem + ':odometer:value';
           #js2_setValue('tag':vElemData);
           v_odo(i) = #st_CtoN(#js2_getDataStr(errMsg));

           vElemData = vElem + ':licensePlate:state';
           #js2_setValue('tag':vElemData);
           v_lpstate(i) = #js2_getDataStr(errMsg);
         endfor;

       endif;

       #js2_cleanup();
       *INLR = *ON;

      /end-free

If we take a close look at this example, we see that before we do anything we call the #js2_init() subprocedure.  While this isn't "required" lets just say it's a good idea.  This subprocedure will most likely be updated in the future to do more than it already does.

Next, we see the last thing we do is call the #js2_cleanup() subprocedure.  Again, this isn't "required" but it's a good idea to do so.  It performs vital cleanup functions (memory de-allocation mainly) and resets global variables, etc.

But, the meat of the changes are the new procedures we use to load data that we want to parse.  In this example we load JSON from a stream file using this:

       #js2_setValue('stmf':'/tmp/jsontestfiles/jsondata.json');
       rc = #js2_loadStmf(errMsg);

The first subprocedure, #js2_setValue is used to set the value of the stream file we want to parse.

The next subprocedure, #js2_loadStmf() loads that data into memory.  It will return the total bytes loaded when successful, or -1 if there is an error.  The optional parameter, errMsg, is used to send back an error message if it's available.

The next big changes are how we actually parse and get the value of a JSON object.  There are actually a couple ways to do this.  One (ugly) way we provide is the use of pointers. Here's a quick example:

D JSONData        S          65535    Based(JSONData@)   
D JSONData@       S               *                      
...

#js2_setValue('tag':'objectName');       
rc = #js2_getData(JSONData@:errMsg);  

...

Now, we didn't include all the code to load the JSON data, but this gives you an idea of how this works.  If successful in finding the JSON object in the loaded string, the first parameter to #js2_getData() will return a pointer to the data.  The return value (rc in this case) will contain the length of the string.

But, because pointers aren't very fun, we also have another subprocedure that will most likely be used 99% of the time.  It will return an actual string, as shown in the new example.

           vElem = 'vehicle[' + %char(i) + ']';
           vElemData = vElem + ':id';
           #js2_setValue('tag':vElemData);
           v_id(i) = #js2_getDataStr(errMsg);

This more friendly subprocedure, #js2_getDataStr returns a string value.  If there is an error or it's not found, the value returned is blank.  But the more important thing to notice is that before we call the #js2_getData() or #js2_getDataStr() subprocedures we use the #js2_setValue to set the "tag" to the JSON object name that we want to retrieve.  In this case it will be vehicle[n]:id where n is the array element number to retrieve.

We encourage users to use these new subprocedures when they can as the old ones will most likely be deprecated and not enhanced or supported for very long.

Brad


Last edited 12/11/2014 at 18:01:01



bvstone

Parsing JSON Arrays with RPG and JSONTOOL

Posted:

Parsing JSON Arrays with RPG and JSONTOOL

You will no doubt run into issues where the JSON data you are working with starts with an array, and not an object.

Because the JSON spec allows starting data with an object or an array, you will need to know how JSONTOOL handles each.  When the data starts with an object, the previous examples apply.  But data that starts with an array has a special circumstance that should be noted.

[
  {"phone_contact" : "123-456-7890"}, 
  {"phone_contact" : "234-567-8901"}, 
  {"phone_contact" : "345-678-9012"}, 
  {"phone_contact" : "456-789-0123"}
]

As you can see, the data starts with a bracket "[" representing that the data set is an array of objects instead of a curly bracket "{" which represents an object.

When data like this is loaded into JSONTOOL (using the newer F.JSON2 subprocedures) the data is automatically "wrapped" in one object named "data".   Below is an example of the end result:

{"data":
  [
    {"phone_contact" : "123-456-7890"}, 
    {"phone_contact" : "234-567-8901"}, 
    {"phone_contact" : "345-678-9012"}, 
    {"phone_contact" : "456-789-0123"}
  ]
}

The reason for this is to keep the functionality of the parser consistent.

If we wanted to read the value of the first element in the array, we would use the following:

tag = 'data[1]:phone_contact';                 
#js2_setValue('tag':tag);                   
phoneNumber = #js2_getDataStr();                

We will also run into issues where we aren't told the number of elements in an array.  In this case we need to loop through reading the data until blank is returned.

i = 1;
tag = 'data[' + %char(i) +']:phone_contact';                 
#js2_setValue('tag':tag);                   
phoneNumber(i) = #js2_getDataStr();

dow (phoneNumber(i) <> ' ');
  i += 1;
  tag = 'data[' + %char(i) +']:phone_contact';                 
  #js2_setValue('tag':tag);                   
  phoneNumber(i) = #js2_getDataStr();
enddo;

Think of this as a method of using a "priming read" before entering the loop.  Something I've done since college after doing it another way caused some issues I'd rather not remember.  :)  There are other ways to do this, but this is how I choose to.

Multi-Dimensional Arrays

In JSON you can also have arrays in your arrays!  Parsing this type of data is similar.  Let's assume the following data:

[
  {"phone_contact" : "123-456-7890"}, 
  {"phone_contact" : "234-567-8901",
   "aliases" : [
     {"name" : "jezza"}, 
     {"name" : "clarkson"}, 
     {"name" : "jeremy"}
   ]},
  {"phone_contact" : "345-678-9012"}, 
  {"phone_contact" : "456-789-0123"}
]

In this example, the 2nd element has another array of objects that describe aliases of the contact, in this case BBC's infamous Jeremy Clarkson of Top Gear ("brilliant", I know).

If we wanted to retrieve the name of the 1st alias (jezza) in the 2nd object (the only one with the alias array), we would use the following:

tag = 'data[2]:aliases[1]:name';                 
#js2_setValue('tag':tag);                   
alias = #js2_getDataStr();    

If we wanted to retrieve the 2nd alias (clarkson) in the 2nd object, we would use the following:

tag = 'data[2]:aliases[2]:name';                 
#js2_setValue('tag':tag);                   
alias = #js2_getDataStr();  

As you can see, we again denote the array element using a number inside of brackets after the object.

All simple enough.  Programming to read this data again can be done by looping (inside your main loop) to load each alias instance.

Interestingly enough, we see here that JSON doesn't really allow us to create an array inside of an object that does not have a name associated with it.  As we saw in the first example, that can only occur when the array is the start of the data.  To me, that is inconsistent, especially for JSON parsers.  That is why we chose to go the route of wrapping arrays in an object before starting to parse.

 


Last edited 05/30/2015 at 07:47:00




Reply




© Copyright 1983-2020 BVSTools
GreenBoard(v3) Powered by the eRPG SDK, MAILTOOL Plus!, GreenTools for Google Apps, jQuery, jQuery UI, BlockUI, CKEditor and running on the IBM i (AKA AS/400, iSeries, System i).