When interfacing data between different entities the JSON or JavaScript Object Notation format is often used because it creates a smaller payload than some other data interchange formats and can be faster to process.
JSON stores data in arrays or associative arrays of key values pairs creating a human readable output stream.
[
{
manufacturer: "Ford",
model: "Escort"
},
{
manufacturer: "Vauxhall",
model: "Victor"
},
{
manufacturer: "BMW",
model: "iX"
},
{
manufacturer: "Tesla",
model: " Model 3"
},
]
One of the ways to process JSON data on the IBM i is to use SQL. Functions have been built into SQL which allow the consumption and process of JSON input and output. Below is a simple example of an ILE RPG program that outputs a JSON data stream to a file from a URL and then consumes the JSON data by using an SQL function to read the file from the IFS and extract the data elements into a data structure.
A test JSON datasource for use with this program is available from https://novagem.co.uk/jsontestdatasource.json
**free
// ILE RPG Program to demonstrate retrieving and consuming an online JSON data soucre from a URL using SQL
ctl-opt dftactgrp(*NO) option(*NOSHOWCPY:*NODEBUGIO) altseq(*EXT) datfmt(*ISO) timfmt(*ISO);
dcl-ds JSONDataDS qualified occurs(5000);
door char(12);
code zoned(2:0);
colour char(8);
size char(8);
type char(12);
finish char(8);
end-ds;
dcl-s #JSONData int(10);
dcl-s JSONData SQLTYPE(CLOB_FILE);
dcl-s JSONDataURL varchar(300);
dcl-s #t int(5);
dcl-s dsplyErrorMsg varchar(52);
dcl-s SQLErrorMsg varchar(1024);
exec sql SET OPTION SRTSEQ=*LANGIDSHR;
exec sql SET OPTION DATFMT=*ISO;
exec sql SET OPTION DATSEP='-';
exec sql SET OPTION TIMFMT=*ISO;
exec sql SET OPTION TIMSEP='.';
exec sql SET OPTION COMMIT=*ALL;
exec sql WHENEVER NOT FOUND CONTINUE;
exec sql WHENEVER SQLERROR CONTINUE;
JSONDataURL='https://novagem.co.uk/jsontestdatasource.json'; // Setup website URL of JSON data output
// Setup SQL CLOB data structure key fields
JSONData_NAME='/tmp/JSONDataFile.json'; // Fully qualified path and name of file to contain JSON output
JSONData_NL=%len(%trimr(JSONData_NAME)); // Length of the fully qualified file name
JSONData_FO=SQFOVR; // File Operation:
// SQFRD = (File Read)
// SQFCRT = (File create / return with error if it already exists)
// SQFOVR = (Create new file or overwrite an existing one)
// SQFAPP = (Append the data to the end of the file or create a new file if it does not exist)
// Get JSON data output from URL and write to to the specified file given in the data structure
exec sql SELECT SYSTOOLS.HTTPGETCLOB(:JSONDataURL, '') INTO :JSONData FROM SYSIBM.SYSDUMMY1;
if SQLCODE<>0;
exsr getSQLErrorMsg;
*INLR=*ON;
return;
endif;
exec sql CLOSE C1;
// Cursor declaration specifying fields, relative to JSON format, for output to data structure
exec sql DECLARE C1 CURSOR FOR
SELECT * FROM JSON_TABLE(GET_CLOB_FROM_FILE('/tmp/JSONDataFile.json'), // Get JSON from file
'$.buildings[*].fixtures[*]' COLUMNS(door CHAR(13) PATH '$.door',
code SMALLINT PATH '$.code',
colour VARCHAR(7) PATH '$.colour',
size VARCHAR(7) PATH '$.size',
type VARCHAR(13) PATH '$.handle.type',
finish VARCHAR(7) PATH '$.handle.finish')
) AS X ORDER BY X.code;
exec sql OPEN C1;
exec sql FETCH NEXT FROM C1 FOR 5000 ROWS INTO :JSONDataDS;
#JSONData=SQLERRD(3); // Number of rows extracted from JSON data
exec sql CLOSE C1;
// Roll through data structure and display each entry for number of rows retrieved
for #t=1 to #JSONData by 1;
%occur(JSONDataDS)=#t;
dsply JSONDataDS;
endfor;
*INLR=*ON;
return;
begsr getSQLErrorMsg;
exec sql GET DIAGNOSTICS CONDITION 1 :SQLErrorMsg=MESSAGE_TEXT; // Get diagnostic message for SQL error
dsplyErrorMsg=SQLErrorMsg;
dsply dsplyErrorMsg;
endsr;
Note the definition for the field “JSONData” with the field type SQLTYPE(CLOB_FILE). This field can be any name you chose and will then form the prefix for all of the data structure key fields that define the SQL CLOB file statement.
This is just a simple example of consuming JSON data. You would probably want to do things such as a “for next” loop for the fetch statement when the exact size of the JSON datasource is unknown. Also the the data source can be directly accessed via URL with an HTTPGETCLOB statement instead of GET_CLOB_FROM_FILE.