-
-
Save RainerRoss/0ad3c98598d71247352427664c4c4252 to your computer and use it in GitHub Desktop.
| { | |
| "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 | |
| } | |
| ] | |
| } |
| 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; | |
| //------------------------------------------------------------------// |
| 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; |
Please give me an example of your JSON
This is an part of the JSON, to keep it simple
[
{
"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,
}
]
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 !
when trying to execute this statement from ACS "run SQL script" I get an error saying that LOB locators are not allowed with commit = *none. Anyone knows how to change commit in ACS. In an RPG program I use exec sql set commit = *chg. it dosn't work on the "run SQL script" screen
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!
How would I read a Substructure?
I have a JSON File, lets say the same one as you, the last field in items[0] would be "Sub-acc", if there is a Sub-acc then the, lets say, name has to be overwritten with the data from Sub-acc
Sub-acc and Items have the same structure, except for Sub-acc which cannot appear again in Sub-acc