RPG Example reading JSON using JSON_TABLE from IFS 

Decode JSON webservice reply data (already stored in IFS) using JSON_TABLE

This reads the JSON from the IFS – decodes it using JSON_TABLE and reports on time taken to perform decode.

In this case the JSON data is a simple layout that looks like this:

{"users":[{"userID":"@123","firstName":"Billy","lastName":"Bob","initials":"123","company":110,"division":30,"department":325,"secProfile":""},{"userID":"AAATEMP","firstName":"Albert","lastName":"Smith","initials":"AAA","company":110,"division":1,"department":1,"secProfile":""},{"userID":"AAATEST","firstName":"Andy","lastName":"Tester","initials":"AAA","company":110,"division":1,"department":530,"secProfile":"AAT"},{"userID":"AAA3","firstName":"Fred","lastName":"Blogs","initials":"AA3","company":110,"division":1,"department":1,"secProfile":"AAA"},{"userID":"AATEST","firstName":"AA","lastName":"TEST","initials":"AAT","company":110,"division":1,"department":1,"secProfile":""},{"userID":"ABB","firstName":"Abba","lastName":"Dancing Queen","initials":"","company":110,"division":1,"department":1,"secProfile":""},{"userID":"BINGBONG","firstName":"Bing","lastName":"Bong","initials":"BB","company":110,"division":2,"department":2,"secProfile":""},{"userID":"LITTENN","firstName":"Nick","lastName":"Litten","initials":"NJL","company":001,"division":1,"department":123,"secProfile":"Secret Squirrel"}]}

This JSON example contains the values:

 USERID varchar(100); 
 FIRSTNAME varchar(100); 
 LASTNAME varchar(100); 
 INITIALS varchar(100); 
 COMPANY int(10); 
 DIVISION int(10); 
 DEPARTMENT int(10);

So. lets read it and break it out into fields, which I am just storing in an array. You could write them to a file just as easily…

 
**FREE

 /TITLE JSON_TABLE Decode JSON using SQL - Proof of Concept

 // PROGRAM CREATION OVERRIDES:
 // Create as a Module and bind into ILE program
 //
 // JSNIFSSQL.sqlrpgle (fully /free)
 //
 // This demonstrates reading JSON data from an IFS file and then
 // parsing that JSON using SQL JSON_TABLE functions.
 //
 // The JSON data is loaded into a data structure suitable
 // for display from a program debugger.
 //
 // MODIFICATION HISTORY:
 //  07/17/2017 nick.litten@projex.com V1.00 LM740287

 ctl-opt dftactgrp(no) actgrp('NICKLITTEN')  option(nodebugio:srcstmt:nounref)
  datfmt(*ISO) decedit('0.')
  copyright('| JSNIFSSQL V1.0 06/27/2017 Use JSON TABLE to read JSON from IFS and parse it into array.');

 // IFS file with JSON code that we will be reading and decoding
 dcl-c ifsFilename
       const('/littenn/getwebjsn.json');
 dcl-s lastElem int(10);
 dcl-s errMsg varchar(500) inz('');
 dcl-s json sqltype(clob:16000000);

 dcl-ds jsonFields qualified;
    USERID varchar(100);
    FIRSTNAME varchar(100);
    LASTNAME varchar(100);
    INITIALS varchar(100);
    COMPANY int(10);
    DIVISION int(10);
    DEPARTMENT int(10);
 end-ds;

 dcl-ds result qualified;
    success ind;
    errmsg varchar(500);
    jsonArray likeds(jsonFields) dim(9999);
 end-ds;

 dcl-s strTimeStamp timestamp inz;       // start of pgm
 dcl-s strJsonTimeStamp timestamp inz;   // start of JSON decode logic
 dcl-s endTimeStamp timestamp inz;       // used to calculate duration
 // use IBM i *APIS to loads IFS into variable
 dcl-s Count int(10);
 dcl-s Handle int(10);
 dcl-s ifsData char(16000000);
 dcl-s ifsDataLen int(10);
 dcl-s rc int(10);
 dcl-s O_RDONLY int(10) inz(1);
 dcl-s O_TEXTDATA int(10) inz(16777216);

 dcl-pr open int(10) extproc('open');
    n pointer value options(string); // filename
    *n int(10) value; // openflags
    *n uns(10) value options(*nopass); // mode
    *n uns(10) value options(*nopass); // codepage
 end-pr;

 dcl-pr read int(10) extproc('read');
    *n int(10) value; // filehandle
    *n pointer value; // datareceived
    *n uns(10) value; // nbytes
 end-pr;

 dcl-pr close int(10) extproc('close');
    *n int(10) value; // filehandle
 end-pr;

 // Parms for logging-text being sent back into program message queue
 dcl-pr QMHSNDPM extpgm('QMHSNDPM');
    *n char(7) const; // MsgID
    *n char(20) const; // MsgFile
    n char(32767) const options(varsize); // MsgData
    *n int(10) const; // MsgDtaLen
    *n char(10) const; // MsgType
    *n char(10) const; // StackEntry
    *n int(10) const; // StackCount
    *n char(4); // MsgKey
    *n char(32767) options(*varsize); // ErrorCode
 end-pr;

 dcl-ds ErrorCode;
    BytesProv int(10) inz(0);
    BytesAvail int(10) inz(0);
 end-ds;

 dcl-s joblogMsg char(200);
 dcl-s MsgKey char(4);

 /Title [---------- MAINLINE ---------- ]

 strTimeStamp=%timestamp();

 // Open the stream file
 Handle = open(%trim(ifsFilename):O_RDONLY + O_TEXTDATA);

 // Loop to read the stream file into variable "ifsData"
 dou ifsDataLen<1;
    Count += 1;
    ifsDataLen=read(Handle:
                    %addr(ifsData):
                    %size(ifsData));
 enddo;

 // Close the stream file
 rc= close(Handle);

 if ifsData <> *blanks;
 // we have the IFS data loaded into variable(ifsData) so lets load
 // that variable into the SQL(clob) and process it using the JSON_TABLE
 // to break the JSON out into fields for processing. NOTE: we are not
 // doing anything with those fields in this program because this is just
 // a proof of concept
    exec SQL set option Naming    = *Sys,
                        Commit    = *None,
                        UsrPrf    = *User,
                        DynUsrPrf = *User,
                        Datfmt    = *iso,
                        CloSqlCsr = *EndMod ;

    strJsonTimeStamp=%timestamp();
    JSON_LEN = %len(%trim(ifsdata));
    JSON_DATA = %trim(ifsData);

    EXEC SQL DECLARE C1 CURSOR FOR
      select *
        from JSON_TABLE(:json, '$'  // read the JSON data from a string
         COLUMNS( NESTED '$.users[*]'
                  COLUMNS (USERID VARCHAR(100) PATH '$.userID',
                           FIRSTNAME VARCHAR(100) PATH '$.firstName',
                           LASTNAME VARCHAR(100) PATH '$.lastName',
                           INITIALS VARCHAR(100) PATH '$.initials',
                           COMPANY INT PATH '$.company',
                           DIVISION INT PATH '$.division',
                           DEPARTMENT INT PATH '$.department')
                )) AS X;

 EXEC SQL OPEN C1;

 exec SQL fetch next from C1 into :jsonFields ;

 dow sqlstt='00000' or %subst(sqlstt:1:2)='01';
   lastelem += 1;
   // Store Datastructure values in next element of 'return array'
   result.jsonArray(lastelem) = jsonFields;
   // here we could do something with each row of data ie: write to file
   // or some other business logic.
   exec SQL fetch next from C1 into :jsonFields ;
 enddo;

 exec SQL close C1;
    result.success = *on;
 else;
    // If unable to load JSON data from the IFS then tell the world
    result.success = *off;
    result.errmsg = 'Bugger! I couldnt read the IFS file';
    DSPLY %trim(result.errmsg);
 endif;

 // Set Ending timestamps and calculate runtime
 endTimeStamp=%timestamp();
 joblogMsg = 'JSNIFSSQL JSON_TABLE Completed with ' +
            %char(lastelem) +
            ' elements. Total runtime:' +
            %char(%diff(endTimeStamp:strTimeStamp:mseconds)) +            '
            SQL JSON_TABLE DECODE only:' +
            %char(%diff(endTimeStamp:strJsonTimeStamp:mseconds));

 // stick that message into the joblog so we can clearly see the runtime
 QMHSNDPM( 'CPF9897'
         : 'QCPFMSG   LIBL'         
         : joblogMsg
         : %len(%trimr(joblogMsg))
         : 'DIAG'
         : '*'
         : 0
         : MsgKey
         : ErrorCode );

 *inlr = *on; 

So what does this do?

(1) reads the JSON from the input IFS File

(2) Stores a timestamp

(3) decodes the JSON using JSON_TABLE and stores all the values in an array

(4) sends a message to the joblog saying how long it took.

In this case :

call JSNIFSSQL 

JSNIFSSQL JSON_TABLE Completed with 91 elements.
 Total runtime:513000 SQL
 JSON_TABLE DECODE only:512000

This was fun to program but #prettydarnslow