June 3


RPG Example reading JSON using JSON_TABLE from IFS

By NickLitten

June 3, 2017


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 :


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

This was fun to program but #prettydarnslow

  • I am evaluating your approach. I broght the member JSNIFSYAJL to a source member with a type of SQLRPGLE. I received a diagnostic error from the compile:
    146 from JSON_TABLE( json, ‘$’ // read the JSON data from a string 014600 03/07/18
    SQL0104 30 146 Position 23 Token ( was not valid. Valid tokens: FOR USE
    Any Thoughts?

  • I have a simple json but it doesn’t seem to find any of the nodes. I’ve used this with many of the nodes in my json but it never finds any. node = yajl_object_find( docNode : XXXXXXXX’ );
    if yajl_is_true( node ); XXXXXXXX (I replace with the node name). Is there a typical reason it wouldn’t find a node?

  • Your above example fits my project very well. Thanks for that.

    But I have a problem…
    The input Json file I am using contains a field value of null.
    example.. “ProductPoNumber”:null,
    . When I exclude this field, the fetch works and all data structure fields
    contain their expected data.
    . When I include this field the fetch returns all data structure fields with
    no values and I receive SQLSTT= 22002

    I’ve used ALWNULL(*USRCTL) and NULLIND with the data structure field.
    But it doesn’t matter.. the Fetch occurs before I can use the %NULLIND.

    Any help would be appreciated.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

    Join the IBM i Community for FREE Presentations, Lessons, Hints and Tips


    Snug CBD

     20% Discount

    I have partnered with SNUG CBD givING you Organic CBD
    20% discount code "NL20"