Re: json_table(httpgetclob, nested array

[Jon Paris <jon.paris@xxxxxxxxxxxxxx>]

Hmmm - did I thank the wrong person - apologies if I did.



> On Nov 15, 2019, at 2:18 PM, Peter Dow <petercdow@xxxxxxxxx> wrote:
>
> Following Booth's idea of keeping my grey cells exercised, I gave this a try.  First, I had to rearrange it so I could copy paste to the green screen (all I have available for this) and tried
>
> SELECT * FROM json_table('
> {
>    "wData_length":14,
>    "SeasOceans":[
>       "Adriatic Sea",
>       "Arctic Ocean",
>       "Atlantic Ocean",
>       "Bay of Bengal",
>       "Caribbean Sea"
>    ]
> }',
>  '$.SeasOceans[*]'
>  COLUMNS( SeasOceans varchar(40) )
>  ) as jt
>
> That got me
>
>  ....+....1....+....2....+....3....+....4
>  SEASOCEANS
>  -
>  -
>  -
>  -
>  -
>  ********  End of data  ********
>
> Which looks like it got the 5 elements of the array, but they were null.  Time to rtfm, which says in part
>
> 1. The JSON_TABLE function consists of three parts.
> 2. 1. The JSON object to be deconstructed.
> 3. 2. A path expression that generates zero or more rows from the JSON
>   object.
> 4. 3. The definition of the result columns to be returned. This
>   includes the column name, the result data type, and a path
>   expression to use to locate the column information.
>
> Following their example, and trying a few variations, I came up with this:
>
>
> SELECT * FROM json_table('
> {
>    "wData_length":14,
>    "SeasOceans":[
>       "Adriatic Sea",
>       "Arctic Ocean",
>       "Atlantic Ocean",
>       "Bay of Bengal",
>       "Caribbean Sea"
>    ]
> }',
>  'lax $'
>  COLUMNS(
>  wData_length dec(3,0) PATH 'lax $.wData_length',
>  SeasOceans varchar(40) PATH 'lax $.SeasOceans[1]'
> ) as jt
>
> which resulted in
>
> ....+....1....+....2....+....3....+....4....+....5....
> WDATA_LENGTH  SEASOCEANS
>       14      Arctic Ocean
> ********  End of data  ********
>
>
> 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.