Re: json_table(httpgetclob, nested array

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.