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
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:
DIAGNOSTIC MESSAGES
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
SKIP WAIT WITH FETCH LIMIT ORDER UNION EXCEPT OFFSET.
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
Description: THE NULL VALUE CANNOT BE ASSIGNED TO OUTPUT HOST
VARIABLE NUMBER BECAUSE NO INDICATOR VARIABLE IS
SPECIFIED
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.
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.
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:
DIAGNOSTIC MESSAGES
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
SKIP WAIT WITH FETCH LIMIT ORDER UNION EXCEPT OFFSET.
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?
Attach the JSON and let’s see?
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
Description: THE NULL VALUE CANNOT BE ASSIGNED TO OUTPUT HOST
VARIABLE NUMBER BECAUSE NO INDICATOR VARIABLE IS
SPECIFIED
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.
Maybe you can add a SQL to set the *NULL to *BLANK?
I found this discussion which might help? https://stackoverflow.com/questions/56117054/replace-null-with-blank-value-sql-server
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