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.



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.