Re: External Returned JSON with double array -- RPG400-L

Hi Jerry,

my last response overlapped - so if you want to read the JSON data form an IFS file, try this:

select *
from json_table(
(select line from table(ifs_read('/IAccess/Ship/RtS/ShippingResponse.json', end_of_line=>'NONE'))),
'$.revenueDetails[*][*]'
columns(
name varchar(50) path '$.name',
description varchar(50) path '$.description',
calc_rate numeric(15, 2) path '$.calc_rate',
revenue_amount numeric(15, 2) path '$.revenue_amount',
freight_class_code varchar(50) path '$.freight_class_code',
id integer path '$.id'
)
);

But as I already wrote - the JSON data has to be well-formed - I have prepared myself a JSON file with your data, that contains:

{
"revenueDetails":[
[
{
"name": "D LIFTGATE",
"description": "DELIVERY LIFTGATE",
"calc_rate": 55,
"revenue_amount": 55,
"freight_class_code": "92.5",
"id": 26
},
{
"name": "Minimum B",
"description": "MINIMUM CHARGE",
"calc_rate": 97,
"revenue_amount": 97,
"freight_class_code": "92.5",
"id": 170
},
{
"name": "Fuel Surc",
"description": "Fuel Surcharge",
"calc_rate": 0.33,
"revenue_amount": 32.01,
"freight_class_code": "92.5",
"id": 68
}
]
]
}

The curly braces at the beginning and end are essential.

If you have problems reading the data, you can try IFS_READ_UTF8 instead of IFS_READ. And of course try it in Run SQL Scripts first - and if you are happy - copy the statement into your RPG code. Of course this should be a cursor - and you have to loop through the rows using FETCH.

As I already offered - if you need more help, you can contact my privately, if you don't want to share more data with the world.

HTH and kind regards,
Daniel

Am 10.12.2025 um 20:39 schrieb Jerry Forss <JForss@xxxxxxxxxxxxx>:

This is what I am attempting. Not sure if it's the right approach.

The Buf looks like this. How do I get it to be the actual json string?

ÀÁÊ¬>Í_ÂÁÊ███é██████████ÂÑ%%¬È?¬/ÄÄ?Í>È¬>Í_ÂÁÊ███████████ÂÑ%
%È?██#█>/_Á███ï <!äê ãè█ñ+àíëèêñáë█ñ+ä███/ÀÀÊÁËË█████████çá(
ëè|ä.█ëè███/ÀÀÊÁËË████&|█â|ì████████ÄÑÈ`███< █äê|ëëá███ËÈ/ÈÁ
¬ÑÀ███ïñ███:Ñø¬Ä?ÀÁ█████████'██Ä?%%ÁÄÈÑ?>¬_ÁÈÇ?À███&███øÑÁÄÁ
Ë█████ÏÁÑÅÇÈ███████ÏÁÑÅÇÈ¬Í_███<â███ÄÍËÈ?_ÁÊ¬>/_Á███ï <!äê ã
è█ñ+àíëèêñáë█ñ+ä███ÂÑ%%¬ÀÑËÈ/>ÄÁ███████ÂÑ%%¬ÀÑËÈ/>ÄÁ¬Í_███(ñ
███ÃÊÁÑÅÇÈ¬ÄÇ/ÊÅÁ██████?ÈÇÁÊÄÇ/ÊÅÁÈ?È/%█████████È?È/%¬ÄÇ/ÊÅÁ
██████████Ê/ÈÁ██████Ê/ÈÁ¬È`øÁ███ã███Ê/ÈÁ¬Í>ÑÈ¬ÀÁËÄ███ã< è███

Stream = '/IAccess/Ship/RtS/ShippingResponse.json';
fd = open(stream: O_RDONLY);
if (fd = 0);

buf = *allx'20';
dow (read(fd: %addr(Buf): %size(Buf)) > 0);
Buf = %ScanRpl('[[' : '[' : Buf);
Buf = %ScanRpl(']]' : ']' : Buf);
enddo;

// Save to new json

endif;

CallP Close(FD);

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jerry Forss
Sent: Wednesday, December 10, 2025 11:28 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: External Returned JSON with double array

Yep, I had my NotePad ++ brain going.

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Brad Stone
Sent: Wednesday, December 10, 2025 11:17 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: External Returned JSON with double array

There are no "lines" or "records" in an IFS file. It's a stream file.

On Wed, Dec 10, 2025 at 11:09 AM Jerry Forss <JForss@xxxxxxxxxxxxx> wrote:

My SQL skills are limited.

Does anyone have a bit of code to share that reads an IFS file, delete
lines and saves?
I have written many pgms that create file and adds records, just not
reads and deleted lines.

They mentioned today they are "sunsetting" the old API in 9 days.

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Brad
Stone
Sent: Wednesday, December 10, 2025 10:51 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: External Returned JSON with double array

Lol...

I remember the day when JSON was going to solve all the issues of non
conforming data layouts... and it just made it worse. But still
better han XML any day of the week. :)

Probably outsourced development. Even AI would do better than that.
:)

On Wed, Dec 10, 2025 at 10:27 AM Jerry Forss <JForss@xxxxxxxxxxxxx> wrote:

Sorry

Because they wrote it so it is right.

-----Original Message-----
From: Jerry Forss
Sent: Wednesday, December 10, 2025 10:26 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: External Returned JSON with double array

I have.

Because

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Brad Stone
Sent: Wednesday, December 10, 2025 10:00 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: External Returned JSON with double array

Scary that this is valid JSON. Have you contacted the people
responsible to ask WF* is this all about? lol I would certainly be
asking them about the structure of that

On Wed, Dec 10, 2025 at 9:25 AM Jerry Forss <JForss@xxxxxxxxxxxxx>

wrote:

I suppose I could open the json, remove the extra [ ], save it and
then use the data-into.
This seems like a silly way to do it but just not figuring out how
to define it correctly.

YAJLGEN doesn't seem to know what to do with it either.

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Jerry Forss
Sent: Wednesday, December 10, 2025 7:17 AM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: External Returned JSON with double array

CAUTION: This email originated from outside of the organization.
Do not click links or open attachments unless you recognize the
sender and know the content is safe.

One of our carriers has a replacement service that returns a json

response.

In the response it has a double array defined and I am having
trouble defining it in my rpg.
How do I define this correctly?

Section of returned json.

"revenueDetails": [
[
{
"name":
"D LIFTGATE",

"description":

"DELIVERY LIFTGATE",

"calc_rate":

55,

"revenue_amount": 55,

"freight_class_code": "92.5",
"id": 26
},
{
"name":
"Minimum B",

"description":

"MINIMUM CHARGE",

"calc_rate":

97,

"revenue_amount": 97,

"freight_class_code": "92.5",
"id": 170
},
{
"name":
"Fuel Surc",

"description":

"Fuel Surcharge",

"calc_rate":

0.33,

"revenue_amount": 32.01,

"freight_class_code": "92.5",
"id": 68
}
]
],

From yajlgen

num_REVENUEDETAILS int(10) inz(0); REVENUEDETAILS varchar(1)
dim(1); // empty;

From my code

cnt_revenueDetails Int(5);
Dcl-ds revenueDetails Dim(25);
name Char(50);
description Char(100);
calc_rate Packed(7 : 3);
revenue_amount Packed(7 : 2);
freight_class_code Char(5);
id Char(5);
End-Ds;
--
This is the RPG programming on IBM i (RPG400-L) mailing list To
post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list To
post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post
a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post
a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.