Re: External Returned JSON with double array

Daniel Gross <[email protected]> Wed, 10 Dec 2025 21:11:26 +0100
Newsgroups	gmane.comp.lang.as400.rpg
Message-ID	<[email protected]>
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 <[email protected]>:
> 
> 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 <[email protected]> On Behalf Of Jerry Forss
> Sent: Wednesday, December 10, 2025 11:28 AM
> To: RPG programming on IBM i <[email protected]>
> Subject: RE: External Returned JSON with double array
> 
> Yep, I had my NotePad ++ brain going.
> 
> -----Original Message-----
> From: RPG400-L <[email protected]> On Behalf Of Brad Stone
> Sent: Wednesday, December 10, 2025 11:17 AM
> To: RPG programming on IBM i <[email protected]>
> 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 <[email protected]> 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 <[email protected]> On Behalf Of Brad
>> Stone
>> Sent: Wednesday, December 10, 2025 10:51 AM
>> To: RPG programming on IBM i <[email protected]>
>> 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 <[email protected]> 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 <[email protected]>
>>> Subject: RE: External Returned JSON with double array
>>> 
>>> I have.
>>> 
>>> Because
>>> 
>>> -----Original Message-----
>>> From: RPG400-L <[email protected]> On Behalf Of
>>> Brad Stone
>>> Sent: Wednesday, December 10, 2025 10:00 AM
>>> To: RPG programming on IBM i <[email protected]>
>>> 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 <[email protected]>
>> 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 <[email protected]> On Behalf Of
>>>> Jerry Forss
>>>> Sent: Wednesday, December 10, 2025 7:17 AM
>>>> To: RPG programming on IBM i <[email protected]>
>>>> 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: [email protected] To subscribe,
>>>> unsubscribe, or change list options,
>>>> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
>>>> or email: [email protected]
>>>> Before posting, please take a moment to review the archives at
>>>> https://archive.midrange.com/rpg400-l.
>>>> 
>>>> Please contact [email protected] for any subscription
>>>> related questions.
>>>> 
>>>> --
>>>> This is the RPG programming on IBM i (RPG400-L) mailing list To
>>>> post a message email: [email protected] To subscribe,
>>>> unsubscribe, or change list options,
>>>> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
>>>> or email: [email protected]
>>>> Before posting, please take a moment to review the archives at
>>>> https://archive.midrange.com/rpg400-l.
>>>> 
>>>> Please contact [email protected] for any subscription
>>>> related questions.
>>>> 
>>>> 
>>> --
>>> This is the RPG programming on IBM i (RPG400-L) mailing list To post
>>> a message email: [email protected] To subscribe,
>>> unsubscribe, or change list options,
>>> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
>>> or email: [email protected]
>>> Before posting, please take a moment to review the archives at
>>> https://archive.midrange.com/rpg400-l.
>>> 
>>> Please contact [email protected] for any subscription
>>> related questions.
>>> 
>>> --
>>> This is the RPG programming on IBM i (RPG400-L) mailing list To post
>>> a message email: [email protected] To subscribe,
>>> unsubscribe, or change list options,
>>> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
>>> or email: [email protected]
>>> Before posting, please take a moment to review the archives at
>>> https://archive.midrange.com/rpg400-l.
>>> 
>>> Please contact [email protected] for any subscription
>>> related questions.
>>> 
>>> 
>> --
>> This is the RPG programming on IBM i (RPG400-L) mailing list To post a
>> message email: [email protected] To subscribe, unsubscribe,
>> or change list options,
>> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
>> or email: [email protected]
>> Before posting, please take a moment to review the archives at
>> https://archive.midrange.com/rpg400-l.
>> 
>> Please contact [email protected] for any subscription
>> related questions.
>> 
>> --
>> This is the RPG programming on IBM i (RPG400-L) mailing list To post a
>> message email: [email protected] To subscribe, unsubscribe,
>> or change list options,
>> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
>> or email: [email protected]
>> Before posting, please take a moment to review the archives at
>> https://archive.midrange.com/rpg400-l.
>> 
>> Please contact [email protected] for any subscription
>> related questions.
>> 
>> 
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: [email protected] To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: [email protected]
> Before posting, please take a moment to review the archives at https://archive.midrange.com/rpg400-l.
> 
> Please contact [email protected] for any subscription related questions.
> 
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list To post a message email: [email protected] To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: [email protected]
> Before posting, please take a moment to review the archives at https://archive.midrange.com/rpg400-l.
> 
> Please contact [email protected] for any subscription related questions.
> 
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: [email protected]
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: [email protected]
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/rpg400-l.
> 
> Please contact [email protected] for any subscription related questions.
> 
-- 
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: [email protected]
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: [email protected]
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact [email protected] for any subscription related questions.