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