Re: External Returned JSON with double array
Daniel Gross <[email protected]>
| Newsgroups | gmane.comp.lang.as400.rpg |
|---|---|
| Message-ID | <[email protected]> |
Hi Jerry,
so - I had some time and tried it - you can use the following SQL to turn the "double-array" into a simply SQL table:
select *
from json_table(
'{"revenueDetails":[[{"name": "D LIFTGATE", ...}]]}',
'$.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'
)
);
It is important, that the "JSON string" contains a well-formed JSON document - that means, that it has to be enclosed in curly braces.
If the structure is more complex, you can use "nested path" like some kind of "sub-select" or "join" inside the JSON document. But to help more, I would need some more information - e.g. the whole document or a larger snippet.
HTH and kind regards,
Daniel
> Am 10.12.2025 um 18:28 schrieb Jerry Forss <[email protected]>:
>
> 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.