Re: sqlrpgle Get_CLOB_from_file(:url) arrays - getting mixed signals

[Booth Martin <booth@xxxxxxxxxxxx>]

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