Re: json_table(httpgetclob, nested array

I've knocked up an extremely simple embedded SQL example here that might be helpful https://github.com/fathert/json-rpg-example

You can deal with your issue below by coalescing away the nulls, as in my example above, or by adding null indicator fields to the fetch. If you want an example of this I can change or add this.

Tim.




________________________________
From: WEB400 <web400-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Booth Martin <booth@xxxxxxxxxxxx>
Sent: 18 November 2019 17:25
To: Web Enabling the IBM i (AS/400 and iSeries) <web400@xxxxxxxxxxxxxxxxxx>
Subject: Re: [WEB400] json_table(httpgetclob, nested array

At this point I am thinking it is my not understanding SQLRPGLE. The
code, used in an SQLRPGLE program, returns SQLCODE error (I have no idea
what this message means.):

Message ID . . . . . . : SQL0305
Date sent . . . . . . : 11/18/19 Time sent . . . . . . :
11:09:52

Message . . . . : Indicator variable required.

Cause . . . . . : A FETCH, embedded SELECT, CALL, GET DESCRIPTOR, or a
SET
or VALUES INTO statement has resulted in a null value, but an indicator
variable was not specified for host variable WSEAS. The relative
position
of the host variable in the INTO clause or parameter list is 3. If
the host
variable name is *N, a descriptor area was specified.
If this error occurs on a GET DESCRIPTOR statement, the null value is
being returned but the INDICATOR item was not specified on the GET
DESCRIPTOR statement.

On 11/18/2019 2:54 AM, Tim Fathers wrote:

Hi Booth,

This is the syntax you need:

select *

from json_table(
'{"wData_length":14,"SeasOceans":["Adriatic Sea","Arctic Ocean","Atlantic Ocean","Bay of Bengal","Caribbean Sea"]}',
'lax $'
columns(
DATA_LEN varchar(20) path '$.wData_length',
nested path '$.SeasOceans[*]'
columns(
SEA varchar(20) path '$'
)
)
) as U

...which gives...

DATA_LEN SEA
-------- --------------
14 Adriatic Sea
14 Arctic Ocean
14 Atlantic Ocean
14 Bay of Bengal
14 Caribbean Sea

As others have said, if you want to process the data row-by-row, you'll need to read it with a cursor but they are usually best avoided if you can, so if you need this data in a file just wrap the whole thing in an insert. Assuming the "wData_length" represents the length of the array then I think it's superfluous anyway, as you will only get back one row per array element so you don't need to care how many elements it's says there are..

Tim.

________________________________
From: WEB400 <web400-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Booth Martin <booth@xxxxxxxxxxxx>
Sent: 18 November 2019 01:32
To: Web Enabling the IBM i (AS/400 and iSeries) <web400@xxxxxxxxxxxxxxxxxx>
Subject: Re: [WEB400] json_table(httpgetclob, nested array

exec sql select * into :wCount, :wSeas
from json_table(systools.httpgetclob(:wURL, ''), '$'
columns(
"wData_length" dec(3,0),
SeasOceans varchar(20) PATH 'lax $.SeasOceans[4]' ));

also works, in that it gives me the count and element[4].

For collecting an array, "nested" seems to be the correct word, probably
also needing ''lax $'. I suspect the issue involves either the SQLRPGLE
usage, or more likely, programmer error. I did try to loop. That gave
me element 1 repeatedly; I could not find a way to use a variable in
place of the $.SeasOcceans[4] and using an * instead of the 4 failed.


..On 11/17/2019 6:02 PM, Jon Paris wrote:

It is going to return a row for each element Booth so you'll probably need to use a cursor.

Does it work without nested?

--
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://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fweb400&amp;data=02%7C01%7C%7Ce781355e77504375accf08d76c43e115%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637096911100117079&amp;sdata=ENZ0NfmUQ3iRYCwxe%2Bnh0iWNrsM3TU%2FYCSZkrkT2rT8%3D&amp;reserved=0
or email: WEB400-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fweb400&amp;data=02%7C01%7C%7Ce781355e77504375accf08d76c43e115%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637096911100117079&amp;sdata=jm9NbseeNDh3oDEJ2jRP5VBM%2Bl2w3GmYnlSX8si5%2FIQ%3D&amp;reserved=0.

--
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://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fweb400&amp;data=02%7C01%7C%7Ce781355e77504375accf08d76c43e115%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637096911100127084&amp;sdata=HFZCxc%2BvAlGx9qhkDNpDqC912JTmPUb5HB2nEz5u2Jg%3D&amp;reserved=0
or email: WEB400-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://nam02.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fweb400&amp;data=02%7C01%7C%7Ce781355e77504375accf08d76c43e115%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637096911100127084&amp;sdata=pjv5bHbBizo990BXWAEclrYBy1lZfLiSzJs0%2FTIsgB0%3D&amp;reserved=0.