Re: json_table(httpgetclob, nested array

[Jon Paris <jon.paris@xxxxxxxxxxxxxx>]

It is going to return a row for each element Booth so you'll probably need to use a cursor.

Does it work without nested?  


> On Nov 17, 2019, at 4:39 PM, Booth Martin <booth@xxxxxxxxxxxx> wrote:
>
> Been a long couple of days.
>
>            exec sql select * into :wCount, :wSeas
>              from json_table(systools.httpgetclob(:wURL, ''), '$'
>                columns(
>                  wData_length dec(3,0) path 'lax $.wData_length',
>                  nested 'lax $.SeasOceans[*]'
>                  columns(
>                  SeasOceans varchar(20) path 'lax $')) );
>
> returns the count and the first array element with an sql message:
>
> "Result of SELECT more than one row.
> SQLCODE = -811"
>
> Which is not incorrect, but I thought was handled by "nested"?  Perhaps it is related to SQLRPGLE more than to SQL?
>
>
>
> On 11/15/2019 1:18 PM, Peter Dow wrote:
> > The documentation I was reading at https://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_73/sqlp/rbafyjsontable.htm 
> >
> > did not have an explanation of the "path". Reading further, I found where it had example of an array within the json object. That got me to
> >
> > SELECT * FROM json_table('
> > {
> >    "wData_length":14,
> >    "SeasOceans":[
> >       "Adriatic Sea",
> >       "Arctic Ocean",
> >       "Atlantic Ocean",
> >       "Bay of Bengal",
> >       "Caribbean Sea"
> >    ]
> > }',
> >  'lax $.SeasOceans[*]'
> >  COLUMNS(
> >  SeasOceans varchar(40) PATH 'lax $')
> > ) as jt
> >
> > which returns
> >
> > ....+....1....+....2....+....3....+....4
> > SEASOCEANS
> > Adriatic Sea
> > Arctic Ocean
> > Atlantic Ocean
> > Bay of Bengal
> > Caribbean Sea
> > ********  End of data  ********
> >
> > I still have no idea what the lax in the path means.  I tried changing it to abc and it didn't complain, but it didn't select anything either.
> >
> > -- 
> > *Peter Dow* /
> > Dow Software Services, Inc.
> > 909 793-9050
> > petercdow@xxxxxxxxx <mailto:petercdow@xxxxxxxxx>
> > pdow@xxxxxxxxxxxxxx <mailto:pdow@xxxxxxxxxxxxxx> /
> -- 
> This is the Web Enabling the IBM i (AS/400 and iSeries) (WEB400) mailing list
> To post a message email: WEB400@xxxxxxxxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/web400
> or email: WEB400-request@xxxxxxxxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/web400.