JSON and IBM i – FAQ & Howto (EN)

This is not intended to be a comprehensive guide to managing JSON with IBM i – AS400 but simply a collection of FAQs, tricks and techniques used in the daily experience or “stolen” from some website or forum.

It wants to be an ever-evolving guide and for this reason I also invite you to grow it by using comments to the article or by reporting your experiences.

iJSON-FAQ-001: Scott Klement’s new YAJLGEN command

Always he, always the great Scott Klement after giving us YAJL and then YAJL-INTO for json management in our RPG programs comes with a fantasizing Command YAJLGEN, included in the Product Open Source YAJL: https://www.scottklement.com/yajl/

With YAJLGEN, you can construct the DS for the RPG DATA-INTO function from any JSON saved in the IFS. Isn’t that great? I take a JSON, launch YAJLGEN that creates me the DS for RPG and a template call to the DATA-INTO function and the work is done …. from there on I have my DS compiled and I do what I want of the data read from the JSON document.

Let’s see an example: I take a JSON also quite complex, with several nestings (taken from Adobe‘s opensource site) :

"root":[
     {
         "id": "0001",
         "type": "donut",
         "name": "Cake",
         "ppu": 0.55,
         "batters":
             {
                 "batter":
                     [
                         "id": "1001", "type": "Regular",
                         "id": "1002", "type": "Chocolate",
                         "id": "1003", "type": "Blueberry",
                         "id": "1004", "type": "Devil's Food"
                     ]
             },
         "topping":
             [
                 "id": "5001", "type": "None" ,
                 "id": "5002", "type": "Glazed",
                 "id": "5005", "type": "Sugar",
                 "id": "5007", "type": "Powdered Sugar",
                 "id": "5006", "type": "Chocolate with Sprinkles",
                 "id": "5003", "type": "Chocolate",
                 "id": "5004", "type": "Maple"
             ]
     },
     {
         "id": "0002",
         "type": "donut",
         "name": "Raised",
         "ppu": 0.55,
         "batters":
             {
                 "batter":
                     [
                         "id": "1001", "type": "Regular"
                     ]
             },
         "topping":
             [
                 "id": "5001", "type": "None" ,
                 "id": "5002", "type": "Glazed",
                 "id": "5005", "type": "Sugar",
                 "id": "5003", "type": "Chocolate",
                 "id": "5004", "type": "Maple"
             ]
     },
     {
         "id": "0003",
         "type": "donut",
         "name": "Old Fashioned",
         "ppu": 0.55,
         "batters":
             {
                 "batter":
                     [
                         "id": "1001", "type": "Regular",
                         "id": "1002", "type": "Chocolate"
                     ]
             },
         "topping":
             [
                 "id": "5001", "type": "None" ,
                 "id": "5002", "type": "Glazed",
                 "id": "5003", "type": "Chocolate",
                 "id": "5004", "type": "Maple"
             ]
     }
 ]
 }

I save the JSON document in the IFS of my IBM i … so I called the YAJLGEN function:

YAJLGEN STMF('/myjson/jsonexample3.json') SRCFILE(FAQ400/SRC) SRC MBR(JSONEXAM)                                                                   

Here is my JSONEXAM source created by the YAJLGEN function: I compile, I debug … Great! … my dS beautiful and filled out!

   ctl-opt dftactgrp(-no);

   readTheJson();
   'inlr'

   FIXME:
   - The field lengths (varchar/packed) are guesses
   and should be adjusted based on your business rules.
   - The array lengths (dim keywords) are also guesses
   and should be adjusted based on your business rules

   dcl-proc readTheJson;

     dcl-ds jsonDoc qualified;
       num_ROOT int(10) inz(0);
       dcl-ds ROOT dim(100);
         ID varchar(4) inz('');
         TYPE varchar(5) inz('');
         NAME varchar(4) inz('');
         PPU packed(3:2) inz(0);
         dcl-ds BATTERS;
           num_BATTER int(10) inz(0);
           dcl-ds BATTER dim(4);
             ID varchar(4) inz('');
             TYPE varchar(30) inz('');
           end-ds;
         end-ds;
         num_TOPPING int(10) inz(0);
         dcl-ds TOPPING dim(7);
           ID varchar(4) inz('');
           TYPE varchar(4) inz('');
         end-ds;
       end-ds;
     end-ds;

     dcl-s ifsPathName varchar(5000);

     ifsPathName : '/myjson/jsonexample3.json';

     data-into jsonDoc %DATA( ifsPathname
                            : 'doc?file case?convert countprefix?num_')
                     %PARSER( 'YAJLINTO'
                            : "document_name": "jsonDoc",
                                 "number_prefix": "YAJL_" ) ;

   end-proc; 

Some attentions:

• as indicated in Scott Klement’s FIXME notes, the size of the Arrays and Alphabetical Fields of DS must be arranged according to your needs … in the above case I changed the size of the array “root” of the variabule “TYPE”
• The JSON taken from Adobe’s site did not have a “root” … started directly as an array with a bracket “[” and not a curly ” . . . I had to add a key-value “root”
• Probably for an incorrect CCSID probelma YAJLGEN brings strange characters in the parameters of the %PARSER of the DATA-INTO specification … that I had to change with open and closed brace … but with the time saved by the utility it was definitely worth it!

iJSON-FAQ-002: How to update YAJL by Scott Klement

Unfortunately to date, August 2019, Scott has not foreseen a real versioning of the excellent YAJL library ( https://www.scottklement.com/yajl/ ) for handling JSON in RPG … instead, it regularly keeps up-to-date with new features. As also suggested by Scott Klement himself, to see the version make a:

DSPSRVPGM SRVPGM(YAJL)

The version to date, August 2019, was compiled on 7/19/2019

If you have an older version of the update you can do it by “quietly passing over” the old library … then, after downloading the SAVF and carrying into the IFS following the instructions of Scott’s Readme.txt … Run the RSTLIB command with one more option:

 RSTOBJ OBJ(-ALL) SAVLIB(QTEMP) DEV(-SAVF) RSTLIB(YAJL) SAVF(QGPL/YAJLLIB72) ALWOBJDIF(-ALL)  

iJSON-FAQ-003: JSON and different arrays, JSON_TABLE or DATA-INTO?

Sometimes we need to deal with JSON documents with different nested arrays and is not so easy to read data from them.

Let’s try with this JSON example from Midrange.com forum post: it’s something like a Google Maps JSON answer with distances for four different addresses:

{
    "destination_addresses" : [
       "1234 Main St, La Crescent, MN 55947, USA",
       "1742 Park Ave, La Crosse, WI 54601, USA",
       "1210 S 10th St, La Crosse, WI 54601, USA",
       "9876 W 52nd Ave, Onalaska, WI 854650 USA"
    ],
    "origin_addresses" : [ "1500 Old Hickory Dr, La Crescent, MN 55947, USA" ],
    "rows" : [
       {
          "elements" : [
             {
                "distance" : {
                   "text" : "18.3 mi",
                   "value" : 29452
                },
                "duration" : {
                   "text" : "29 mins",
                   "value" : 1746
                },
                "status" : "OK"
             },
             {
                "distance" : {
                   "text" : "9.5 mi",
                   "value" : 15308
                },
                "duration" : {
                   "text" : "18 mins",
                   "value" : 1056
                },
                "status" : "OK"
             },
             {
                "distance" : {
                   "text" : "3.8 mi",
                   "value" : 6142
                },
                "duration" : {
                   "text" : "9 mins",
                   "value" : 534
                },
                "status" : "OK"
             },
             {
                "distance" : {
                   "text" : "19.3 mi",
                   "value" : 31032
                },
                "duration" : {
                   "text" : "22 mins",
                   "value" : 1331
                },
                "status" : "OK"
             }
          ]
       }
    ],
    "status" : "OK"
 }

Let us assume that our JSON is an IFS file (‘/home/FAQ400/gmaps.json’): let’s try to parse it using only SQL functions, JSON_TABLE and GET_CLOB_FROM_FILE and some JOIN and CROSS JOIN …

select * from
 ( 
 SELECT *
             from
                 JSON_TABLE(get_clob_from_file('/home/FAQ00/gmaps.json'),
                 '$'  COLUMNS(
                 origin_addresses VARCHAR(30)  PATH '$.origin_addresses'
                  ) ) as x 
 ) cc
 cross join table
 (
 SELECT  ROW_NUMBER() OVER () rownumber, a.*
             from
                 JSON_TABLE(get_clob_from_file('/home/FAQ00/gmaps.json'),
                 '$.rows.elements'  COLUMNS(
                  distance_text VARCHAR(30)  PATH '$.distance.text',
                  distance_value VARCHAR(30)  PATH '$.distance.value',
                  duration_text VARCHAR(30)  PATH '$.duration.text',
                  duration_value VARCHAR(30)  PATH '$.duration.value',
                  statust VARCHAR(30)  PATH '$.status'
              ) ) as  a 
 ) aa
 join table 
 (
 SELECT ROW_NUMBER() OVER () rownumber, b.*
             from
                 JSON_TABLE(get_clob_from_file('/home/FAQ00/gmaps.json'),
                 '$.destination_addresses'  COLUMNS(
                  Destination_addressess VARCHAR(30)  PATH '$'
                  ) ) as b 
 ) bb 
 on aa.rownumber=bb.rownumber 
              ;                                

We could parse the same JSON file with DATA-INTO RPG function and, thanks to Saint Scott, with YAJLGEN it won’t take but a minute to have our DS and DATA_INTO RPG example (take a look at “iJSON-FAQ-001” above)

   ctl-opt dftactgrp(*no);

   readTheJson();
   *inlr = *on;

   // FIXME:
   //   - The field lengths (varchar/packed) are guesses
   //       and should be adjusted based on your business rules.
   //   - The array lengths (dim keywords) are also guesses
   //       and should be adjusted based on your business rules

   dcl-proc readTheJson;

     dcl-ds jsonDoc qualified;
       num_DESTINATION_ADDRESSES int(10) inz(0);
       DESTINATION_ADDRESSES varchar(40) inz('') dim(4);
       num_ORIGIN_ADDRESSES int(10) inz(0);
       ORIGIN_ADDRESSES varchar(47) inz('') dim(1);
       num_ROWS int(10) inz(0);
       dcl-ds ROWS dim(1);
         num_ELEMENTS int(10) inz(0);
         dcl-ds ELEMENTS dim(4);
           dcl-ds DISTANCE;
             TEXT varchar(7) inz('');
             VALUE packed(5) inz(0);
           end-ds;
           dcl-ds DURATION;
             TEXT varchar(7) inz('');
             VALUE packed(4) inz(0);
           end-ds;
           STATUS varchar(2) inz('');
         end-ds;
       end-ds;
       STATUS varchar(2) inz('');
     end-ds;

     dcl-s ifsPathName varchar(5000);

     ifsPathName = '/smedoc/UTE/DEPE/gmaps.json';

     data-into jsonDoc %DATA( ifsPathname
                            : 'doc=file case=convert countprefix=num_')
                     %PARSER( 'YAJLINTO'
                            : '{ "document_name": "jsonDoc", +
                                 "number_prefix": "YAJL_" }');

   end-proc;               

iJSON-FAQ-004: When a JSON is not a real JSON

Sometimes whe have to deal with web services and JSON documents not in in the “normal form”

 { key : value } 

but in an “array form”

 [{ key : value }, { key : value }, ...}]

To parse those JSON documents with JSON_TABLE or other funtions we have to get the “normal form” … take a look at this Scott Forstie post: “JSON_TABLE and survival tips for shredding JSON with SQL“

iJSON-FAQ-006: JSON and ILE RPG or SQL examples by Tim Fathers

Do you need some JSON examples in RPG and SQL… take a look at this Git repository by Tim Fathers

Json-RPG-Example

iJSON-FAQ-007: DATA-INTO with Multi-object JSON and arrays (and some Scott Klement’s advice)

I draw inspiration from Midrange.com’s mailing list (Path and DATA-INTO and YAJLINTO) to analyze a real case of multi-objects JSON and arrays with DATA-INTO RPG Opcode and YAJLINTO parser (and some Scott Klement’s advice too!)

You can find all sources and example JSON on our GIST repo : Faq400Git/Data-Into-Multi-Objcets-Arrays

Let’s take a look at the JSON document

{
     ..
    "data": {
      ...
      "Invoice": [
            {
                "CreatedTimestamp": "2019-12-04T22:30:32.766",
                 ...
            },
            {
                "CreatedTimestamp": "2019-12-04T22:30:32.766",
                 ...
            }
                ],

As you can see, we have a data structure with an unnamed outermost json element (start with a opening curly bracket), then we have an “data” object and an “Invoice” array

The options of DATA-INTO and Parser used

• To manage the object without a more external name, YAJLINTO allows you to name it using the options, in the example we see “document_name”: “json” just to give it the json name to the object itself
• If we are interested in the data of the individual invoices (Invoice) we can indicate a PATH in the options of DATA-INTO to get inside that detail: ” path=json/data/Invoice ” … as you can see we use “json” as the name of the outermost object
• Another thing we see in the JSON are Timestamp type values … that the JSON does not handle except as string fields … so the best thing to do when you have Timestamp values is to put them in char fields of 26 char in our DS RPG

Basically the above options are set as in the source piece below

// Options      
       RPGOPTS=' path=json/data/Invoice'
               +' doc=file'
               +' case=any'
               +' allowextra=yes'
               +' allowmissing=yes';

        YAJLOPTS='{"value_null":"0" '
              +', "document_name": "json" '
              +'}';
 // Parsing JSON
        data-into Invoice %DATA( ifsPathname : RPGOPTS)
                          %PARSER( 'YAJL/YAJLINTO' : YAJLOPTS);

How many elements does the array have inside the JSON?

When we have an array inside the JSON “[ {…},{…}]”, if we are analyzing the level of the array itself, YAJLINTO also returns the number of elements found in the PSDS of the program and consequently retrieve it after the DATA-INTO, as in the example following the numElements variable:

 dcl-ds pgmStat psds;
           numElements int(20) pos(372);
 end-ds;

...
 data-into Invoice %DATA( ifsPathname : RPGOPTS)
                          %PARSER( 'YAJL/YAJLINTO' : YAJLOPTS);
 for i=1 to  numElements;
        string='InvoiceId(' +%editc(i:'Z')+ '): '
                 +invoice(i).InvoiceId;
        ...
 endfor;

Scott Klement’s advice

In the forum’s post, Scott Klement recommends using the options “allowextra=yes and allowmissing=yes” only if our DS does not reflect our JSON: if instead, we have the certainty of JSON better create the DS that reflects and avoid those two options that slow down the execution of the DATA-INTO.

Another thing we often find in the DATA-INTO options is “countprefix=xxx” and then in the parser options “number_prefix”: “xxx” … these two settings are useful when you have JSON elements that start as a number, for example { “30-day-outlook”: [ xxxx ] } … in this case the field should be called xxx_30-day-outlook in our DS

In the GIST sources you can also find an example (XDATAINTO3_RPGLE) that parses with DATA-INTO using a DS with all the object layers present in the JSON (pay attention that, in this case, the parser YAJLINTO will not return the number of elements found in the array because the outermost element is not itself an array!).

iJSON-FAQ-008: YAJL and JSON inside another JSON

If I have a Json document “preformatted” and I need to insert it in another JSON I can use YAJL_addPreformattedPtr

Here an example:

YAJL_beginObj();
   YAJL_addBool('success' : stdOutput.success);
   YAJL_addChar('errCode' : stdOutput.errCode);
   YAJL_addChar('message' : stdOutput.message);
   YAJL_addPreformattedPtr('data'
                          : %addr(stdOutput.data:*DATA)
                          : %len(%trimr(stdOutput.data));
YAJL_endObj();

YAJL_copyBuf(CCSID
    : %addr(jsonData)
    : %size(jsonData)
    : wRetLen);
YAJL_genClose();

--- Roberto De Pedrini Faq400.com