Last active
December 28, 2023 14:48
-
-
Save RainerRoss/0ad3c98598d71247352427664c4c4252 to your computer and use it in GitHub Desktop.
Read JSON from IFS in RPGLE
This file contains 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 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 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; |
So in this case, there would be an total of 2 Records in the Table.
One for the the first node without the Sub-acc fields + Sub-acc fields and the second one for the second node
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
)
)
)
);
Wow, thank you very much, Dankeschön and Hvala lijepo !
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This is an part of the JSON, to keep it simple