Re: json_table(httpgetclob, nested array

[Booth Martin <booth@xxxxxxxxxxxx>]

  At the moment my thinking is that the final answer will contain 
"httgetclob" and "nested" in some way, and quite likely will also 
include "lax."

Another stumbling block for me is the "into" clause, which I need since 
I am using SQLRPGLE; it just hiccoughs when there is an array as the "into."



On 11/15/2019 1:18 PM, Peter Dow 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> /