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.

  • Thank you, the above example is clear and very helpful for me as beginner to use JSON with DB2. It works fine with smaller JSON (ifs file) but my original project JSON response file has around 2800 character. Do when I tried to use the above example it is truncating the ifs data and unable to get entire data. Could you please help how I can I use the above example to read and parse large JSON data in ifs file.

    Thanks in advance

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

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