Re: JSON un-named arrays -- RPG400-L

And above reading JSON from the IFS, there are also functions to do web service calls from SQL with http and https:

https://www.ibm.com/docs/en/i/7.3?topic=functions-http-get

You can use this at the same place - instead of putting the JSON there in a char literal, you can also make the HTTP_GET call, to retrieve the JSON document as a CLOB from the web service directly.

I even have an small example on my blog:

https://qpgmr.de/ibmi-sql-json_table

Today, I always look first into the SQL manual, if there is something - SQL has evolved so much in the last years, that there are very rare situations, where I need some internal or external API, to do the job.

HTH
Daniel

Am 18.07.2023 um 00:17 schrieb VERNON HAMBERG Owner via RPG400-L <rpg400-l@xxxxxxxxxxxxxxxxxx>:

Hi Greg

As for how to do this with stream file, if you mean how do you process a JSON file in the IFS, there is an SQL function that can pull the JSON file in to be processed - it's used where Daniel has "insert your json here" - here's an example - JSON_TABLE(Get_CLOB_From_File(:xmlFilePath),

HTH
Vern

On Mon, 17 Jul, 2023 at 4:05 PM, Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

To: rpg programming on ibm i

Thanks Daniel!

With this API, the Data portion is only dynamic based on my call to the REST web service (I passed the value in the "fields" as a parameter to the web service).

I'm willing to try something new (and I like Run SQL Scripts)...

Just curious how I would use this with a stream file on the IFS? I like to save my latest response on the IFS.

Thx,
Greg

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of Daniel Gross
Sent: Monday, July 17, 2023 4:19 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx<mailto:rpg400-l@xxxxxxxxxxxxxxxxxx>>
Subject: Re: JSON un-named arrays

Hi Greg,

I don't know, if it helps, but you can walk the whole JSON document with SQL without problems.

select *
from json_table(
'{...insert.your.json.here...}',
'lax $'
columns (
records integer path '$.records',
fields varchar(1000) path '$.fields',
nested path 'lax $.data[*]'
columns (
trano integer path '$[0]',
ref varchar(50) path '$[1]',
duedate decimal(8, 0) path '$[2]',
name varchar(50) path '$[3]',
amount decimal(10, 2) path '$[4]'
)
)
) as json_doc;

As you can see, the named parts are referenced by ".name", whereas the "unnamed" JSON elements have no named reference, but only the "[index]". The nested path reference is the JSON equivalent of an table JOIN.

Of course - if the "data" part of the JSON is dynamic, and you have to detect the columns be the "fields" element, it can get a but complicated.

But even then, you can first split the "fields" list into its elements, an then construct a dynamic SQL to extract the "data" parts.

So from my POV - SQL is often the easier solution - especially because you can test it with Run SQL Scripts until everything is "perfect".

HTH and kind regards
Daniel

Am 17.07.2023<http://17.07.2023> um 17:31 schrieb Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>>:

Typically, I use DATA-INTO and the YAJLINTO parser... But I'm not sure if that's possible with the JSON below. The array "data" contains an un-named array of values.

Any suggestions on how handle reading this?
Do I need to use the YAJL "tree-based" method?

{
"records": 4,
"fields": "trano,ref,duedate,name,amount",
"data": [
[
1,
"40061433",
20230719,
"Mudpie",
187.2<http://187.2>
],
[
2,
"40061349",
20230719,
"kiddywampus",
156.21<http://156.21>
],
[
3,
"40062677",
20230721,
"Chase",
70.8<http://70.8>
],
[
4,
"40061703",
20230723,
"Stamford Toys",
110.7<http://110.7>
]
]
}
[Logo]<https://www.totalbizfulfillment.com/><https://www.totalbizfulfillment.com/>> Greg Wilburn
Director of IT
301.895.3792 ext. 1231
301.895.3895 direct
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx><mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>>
1 Corporate Dr
Grantsville, MD 21536
www.totalbizfulfillment.com<http://www.totalbizfulfillment.com><http://www.totalbizfulfillment.com<http://www.totalbizfulfillment.com>>

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx<mailto: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<mailto: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<mailto: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<mailto: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<mailto: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<mailto: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.