Re: json_table(httpgetclob, nested array

I played around with it Booth - once I had the format of the json - but could not get it to work. There are some real oddball things in SQL Json processing that I can't get my head around.

I don't believe it is the httpgetclob that gives you a problem because if you just feed the json into json_table like I did below I could still not get int to work. Is it possible that the form of this particular array data is one of the edge cases that SQL cannot handle? I don't know.

I tried many different "flavours" of this:

SELECT * FROM
json_table('{"wData_length":14,"SeasOceans":["Adriatic Sea","Arctic Ocean","Atlantic Ocean","Bay of Bengal","Caribbean Sea"]}',
'$.SeasOceans[*]' COLUMNS( SeasOceans varchar(40) ) ) ;

But could not get it to work. This particular version complains about unexpected end of statement but I have noBest I was able to achieve was to get the count and the first entry of the array.

I strongly suggest you try the approach I have indicated above and get the json parsing right first and _then_ use the httpgetclob. Just debugging technique - get each step in turn working. Then you'll know where the real problem lies.

On Nov 15, 2019, at 12:33 PM, Booth Martin <booth@xxxxxxxxxxxx> wrote:

At first I believed the lack of responses to my question was that my question was too simple and/or too idiotic.

Now I am suspicious that either httpgetclob was not well understood by everyone else too, or httpgetclob itself has issues and no one involved is talking. Or both.

In any event, my goal is to keep my brain active so this weekend I shall be cursoring. Thank you Anshul.


On 11/15/2019 8:52 AM, anshul narang wrote:

Hello Booth .

For reading multiple elements in array , you need to declare cursor and
read the input string.

Please find below example and it works for me .

Input - {"JSON_Exc":[{"abc":12963,"abc1":test},
{"abc":12964,"abc1":test2}]}

Sqlrpgle code.
exec SQL

declare list_Exception_Cursor insensitive scroll cursor for

select *

from JSON_TABLE(:json, '$.JSON_Exc[*]'

COLUMNS(

abc INTEGER PATH '$.abc',
abc1 CHAR PATH '$.abc1')

Then use fetch for cursor.

Thanks
Anshul








On Thu, Nov 14, 2019 at 2:11 PM Booth Martin <booth@xxxxxxxxxxxx> wrote:

raw:

{"wData_length":14,"SeasOceans":["Adriatic Sea","Arctic Ocean","Atlantic
Ocean","Bay of Bengal","Caribbean Sea", ...]

formatted:

wData_length 14
SeasOceans
0 "Adriatic Sea"
1 "Arctic Ocean"
2 "Atlantic Ocean"
3 "Bay of Bengal"
4 "Caribbean Sea"


The array is 100 elements, of which only 14 have data.


On 11/14/2019 7:46 AM, Jon Paris wrote:

What does the JSON look like Booth? Kinda hard to map without knowing

what it looks like.

On Nov 13, 2019, at 10:37 PM, Booth Martin <booth@xxxxxxxxxxxx> wrote:

I am consuming json data with a numeric field and with a 100-element

array. I get the numeric field OK but the array fails. It looks like I
need to use "nested" but every way I have tried to use it fails.

exec sql select "SeasOceans" into :wSeas
from json_table(systools.httpgetclob(:wURL, ''), '$'
columns(
nested '$.Oceans[*]'
columns("SeasOceans" varchar(20) ) ) error on error

);

I am using an SQLRPGLE program for this and asked about this last week

on the RPG list with no response.

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

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

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