Forums >> Programming >> Proof of Concept (POC)
Parsing JSON with RPG and JSONTOOL
by: bvstone

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





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-2017 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).