@1: If you also want to have the statecount, you need 2 nested column entries. If not check my example, it works.
You always need a nested definition if you want to show information from the superior level and the array information.
@2/@3: If we get the information that the first array includes 100 elements and the second one 30, much better, but SQL, i.e. the JSON_TABLE does not care about the number of entries, because a table with an undefined number of rows is returned.
@4: Why you want a SELECT INTO? Declare a Cursor and loop through the result, it is a regular SELECT statement.
When Fetching the row, you can decide what ever you want to do with the information.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them and keeping them!"
„Train people well enough so they can leave, treat them well enough so they don't want to.“ (Richard Branson)
-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Booth Martin
Sent: Freitag, 29. November 2019 20:37
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: sqlrpgle Get_CLOB_from_file(:url) arrays - getting mixed signals
Yes, that is the table I hope to get. Additional information:
* because there are two arrays Would I need two "nested" entries?
* array "states[]" is 100 elements. "statecount" tells us there are 3
"states" elements with values.
* array "cities[]" 1s 30 elements. "citycount" tells us there are 1,
1, and 3 "cities" elements with values within the "states" elements.
* Because I am using SQLRPGLE there must be an "into" clause and that
can not include a dimensioned field.
Thank you for your replies. They are clear and they are helpful.
On 11/29/2019 1:27 AM, Birgitta Hauser wrote:
This seems not to be a problem of GET_CLOB_FROM_FILE, but a problem of our JSON_TABLE Statemen.
What exactly do you want to get returned?
A table like this:
STATE CITY
Alaska Anchorage
Arkansas Stumptoe
Florida Jacksonville
Florida Tampa
Florida Orlando
If so, the SQL Statement must look as follows:
Select *
From JSON_TABLE('{"statecount":3,"states":[
{"state":"Alaska","citycount":1,"cities":["Anchorage","","",""]},
{"state":"Arkansas","citycount":1,"cities":["Stumptoe","","",""]},
{"state":"Florida","citycount":3,"cities":["Jacksonville","Tampa","Orlando",""]}
]}',
'$.states[*]'
Columns(State VarChar(30) path '$.state',
Nested '$.cities[*]'
Columns(City VarChar(30) path '$'))) x
Where City > '';
I added a where condition because several array elements are empty.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
--
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@xxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
