Re: json_table(httpgetclob, nested array

  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> /