Last active
July 31, 2026 14:48
-
-
Save RainerRoss/0ad3c98598d71247352427664c4c4252 to your computer and use it in GitHub Desktop.
Read JSON from IFS in RPGLE
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| { | |
| "items": [ | |
| { | |
| "id": 1, | |
| "name": "MyFirstCompany", | |
| "country": "DE", | |
| "zip": "12559", | |
| "city": "Berlin", | |
| "sales": 15000.59 | |
| }, | |
| { | |
| "id": 2, | |
| "name": "MySecondCompany", | |
| "country": "DE", | |
| "zip": "33739", | |
| "city": "Bielefeld", | |
| "sales": 189500 | |
| } | |
| ] | |
| } |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| ctl-opt dftactgrp(*no) option(*nodebugio:*nounref); | |
| //------------------------------------------------------------------// | |
| // // | |
| // Get JSON from IFS // | |
| // // | |
| //----------------- // | |
| // R.Ross 07.2018 * // | |
| //------------------------------------------------------------------// | |
| // Array SQL-Result // | |
| //------------------------------------------------------------------// | |
| dcl-ds DsResult qualified; | |
| id int(10); | |
| name varchar(30); | |
| country varchar(02); | |
| zip varchar(10); | |
| city varchar(30); | |
| sales packed(12:2); | |
| end-ds; | |
| //------------------------------------------------------------------// | |
| // Process // | |
| //------------------------------------------------------------------// | |
| main(); | |
| *inlr = *on; | |
| //------------------------------------------------------------------// | |
| // Main // | |
| //------------------------------------------------------------------// | |
| dcl-proc Main; | |
| exec sql set option datfmt=*iso, timfmt=*iso, commit=*chg, | |
| closqlcsr=*endactgrp; | |
| exec sql declare cursor01 cursor for // Declare Cursor | |
| Select * from JSON_TABLE( | |
| get_clob_from_file('/tmp/json/customers.json'), | |
| '$' | |
| Columns( | |
| nested '$.items[*]' columns( | |
| "id" integer, | |
| "name" varchar(30), | |
| "country" varchar(02), | |
| "zip" varchar(10), | |
| "city" varchar(30), | |
| "sales" dec(12, 2) | |
| ) | |
| ) | |
| ) x; | |
| exec sql open cursor01; // Open Cursor | |
| dou sqlcode < *zero or sqlcode = 100; | |
| exec sql fetch cursor01 into :DsResult; | |
| if sqlcode >= *zero and sqlcode <> 100; | |
| DsResult = DsResult; // Debug | |
| endif; | |
| enddo; | |
| exec sql close cursor01; // Close Cursor | |
| end-proc; | |
| //------------------------------------------------------------------// |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| select * from json_table ( | |
| get_clob_from_file('/tmp/json/customers.json'), | |
| '$' | |
| columns( | |
| nested '$.items[*]' columns( | |
| "id" integer, | |
| "name" varchar(30), | |
| "country" varchar(02), | |
| "zip" varchar(10), | |
| "city" varchar(30), | |
| "sales" dec(12, 2) | |
| ) | |
| ) | |
| ) as x; |
Hi,
that's quite clear - but how would I define the datastructure to receive it with the sql cursor? I tried substructures, but the compiler complaines with SQL0312, reason code 46 "A member of the host structure is a structure. "
here is the solution for this JSON string
{ "data": [ { "FBBUMON": "202306", "FBFCO": "350", "FBKTOS": "330000", "FBSPLTG": "", "FBKOST": "", "FBBETR": 0.00, "FBKDNR": 0, "SUB-ACC": [ { "FBKOST": "BAZM", "FBBETR": -559.65 } ] }, { "FBBUMON": "202306", "FBFCO": "350", "FBKTOS": "330000", "FBSPLTG": "", "FBKOST": "", "FBBETR": 0.00, "FBKDNR": 0 } ] }Select * from JSON_TABLE( get_clob_from_file('/tmp/json/customers.json'), '$' columns( nested '$.data[*]' columns( FBBUMON varchar(06) path '$.FBBUMON' default '' on empty, FBFCO varchar(03) path '$.FBFCO' default '' on empty, FBBETR dec(12, 2) path '$.FBBETR' default 0 on empty, nested '$.SUB-ACC[*]' columns( FBKOST varchar(04) path '$.FBKOST' default '' on empty, FBBETR2 dec(12, 2) path '$.FBBETR' default 0 on empty ) ) ) );
Author
Hi Herbert, this is the solution
ctl-opt main(main) dftactgrp(*no) option(*nodebugio:*nounref);
//------------------------------------------------------------------//
// //
// Get JSON from IFS //
// //
//----------------- //
// R.Ross 07.2026 * //
//------------------------------------------------------------------//
// SQL Options //
//------------------------------------------------------------------//
exec sql set option datfmt=*iso, timfmt=*iso, commit=*chg,
closqlcsr=*endactgrp;
//------------------------------------------------------------------//
// Array SQL-Result //
//------------------------------------------------------------------//
dcl-ds DsResult qualified;
FBBUMON varchar(06);
FBFCO varchar(03);
FBBETR packed(12:2);
FBKOST varchar(04);
FBBETR2 packed(12:2);
end-ds;
//------------------------------------------------------------------//
// Main //
//------------------------------------------------------------------//
dcl-proc Main;
exec sql declare cursor01 cursor for
select * from JSON_TABLE(
get_clob_from_file('/home/import/json/data.json'), '$'
columns(
nested '$.data[*]' columns(
FBBUMON varchar(06) path '$.FBBUMON' default '' on empty,
FBFCO varchar(03) path '$.FBFCO' default '' on empty,
FBBETR dec(12, 2) path '$.FBBETR' default 0 on empty,
nested '$.SUB-ACC[*]' columns(
FBKOST varchar(04) path '$.FBKOST' default '' on empty,
FBBETR2 dec(12, 2) path '$.FBBETR' default 0 on empty
)
)
)
);
exec sql open cursor01;
dou sqlcode < *zero or sqlcode = 100;
exec sql fetch cursor01 into :DsResult;
if sqlcode >= *zero and sqlcode <> 100;
DsResult = DsResult; // Debug
endif;
enddo;
exec sql close cursor01;
end-proc;
//------------------------------------------------------------------//
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
the only field I'm interested in is the user id (don't really need the values for the other fields. Here is the statement:
Select * from JSON_TABLE(
get_clob_from_file('/QNTC/nws-fs1/GoTo/user_activity_Summary.json'),
'$'
Columns(
nested 'lax $.items[]' columns(
"altName" varchar(30) path '$.userAlternativeNames[]',
" gOToId" varchar(50) path '$.userid',
"userName" varchar(30) path '$.username'
Here is the JSON:
{
"items": [
{
"userAlternativeNames": [],
"userId": "c9c17d7e-a4b8-4a53-bbaa-21d7971e6a14",
"userName": "Jim Lindall",
"dataValues": {
"inboundVolume": 29233,
"inboundDuration": 1480791000,
"outboundVolume": 4572,
"outboundDuration": 1053208000,
"averageDuration": 288413,
"volume": 33805,
"totalDuration": 2533999000,
"inboundQueueVolume": 20244
}
},
{
"userAlternativeNames": [],
"userId": "73445984-043d-4fa1-9943-db6a2ffa7a52",
"userName": "Matt Cook",
"dataValues": {
"inboundVolume": 27154,
"inboundDuration": 1363732000,
"outboundVolume": 2748,
"outboundDuration": 636689000,
"averageDuration": 304292,
"volume": 29902,
"totalDuration": 2000421000,
"inboundQueueVolume": 15813
}
},
{
"userAlternativeNames": [],
"userId": "ce62c7d7-1d0e
If I execute this statement inside an RPG program, it runs. However, all I get is - (hyphens) for values.
Has anyone run into this?
Thank you!