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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2022 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.