× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. July 2023

Re: JSON un-named arrays

I had one just the other day that had an array for a descriptions for an
item:

item: x
descriptions: [
desc1, desc2, desc3, desc4
]

Then in the another document they did
item: x
description_1 : xxx,
description_2 :xxx,
....

up to 23. Why 23? Who knows.

On Wed, Jul 19, 2023 at 5:00 PM Jon Paris <jon.paris@xxxxxxxxxxxxxx> wrote:

The ones I hate are those that introduce arbitrarily named elements into
the stream - apparently "because they can".


Jon P.

On Jul 19, 2023, at 5:09 PM, Brad Stone <bvstone@xxxxxxxxx> wrote:

Don't you love how you can deal with 100 different JSON endpoints and get
100 different interpretations of JSON formatting? lol.

On Wed, Jul 19, 2023 at 4:00 PM Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:

Vern,

Thank you! That works perfect...

However, the API developer added a parameter to the call that causes it
to
respond with element names. I was going to try something new, but it
was
too easy to complete the task with DATA-INTO.

Thanks to all! I learned something.

Greg

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
VERNON
HAMBERG Owner via RPG400-L
Sent: Tuesday, July 18, 2023 11:00 AM
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Cc: VERNON HAMBERG Owner <vhamberg@xxxxxxxxxxxxxxx>;
rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: RE: JSON un-named arrays

Greg


You need to add with chg at the end of the statement - there might be
other commit settings one could use, but that's what I and others here
have
used.


Good luck
Vern


On Tue, 18 Jul, 2023 at 8:06 AM, Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx> wrote:


To: rpg programming on ibm i

Vern,

That's what I tried... I get an SQL Error: SQL State 42926, Sql0443 LOB
and XML locators are not allowed with commit(*none).

I can't find how to overcome that.

Greg

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:
rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>> On Behalf Of VERNON HAMBERG Owner
via RPG400-L
Sent: Monday, July 17, 2023 6:17 PM
To: rpg400-l@xxxxxxxxxxxxxxxxxx<mailto:rpg400-l@xxxxxxxxxxxxxxxxxx>
Cc: VERNON HAMBERG Owner <vhamberg@xxxxxxxxxxxxxxx<mailto:
vhamberg@xxxxxxxxxxxxxxx>>; rpg400-l@xxxxxxxxxxxxxxxxxx<mailto:
rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: JSON un-named arrays

Hi Greg


As for how to do this with stream file, if you mean how do you process a
JSON file in the IFS, there is an SQL function that can pull the JSON
file
in to be processed - it's used where Daniel has "insert your json here"
-
here's an example - JSON_TABLE(Get_CLOB_From_File(:xmlFilePath),


HTH
Vern


On Mon, 17 Jul, 2023 at 4:05 PM, Greg Wilburn <
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>>
wrote:


To: rpg programming on ibm i

Thanks Daniel!

With this API, the Data portion is only dynamic based on my call to the
REST web service (I passed the value in the "fields" as a parameter to
the
web service).

I'm willing to try something new (and I like Run SQL Scripts)...

Just curious how I would use this with a stream file on the IFS? I like
to save my latest response on the IFS.

Thx,
Greg

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:
rpg400-l-bounces@xxxxxxxxxxxxxxxxxx><mailto:
rpg400-l-bounces@xxxxxxxxxxxxxxxxxx<mailto:
rpg400-l-bounces@xxxxxxxxxxxxxxxxxx>>> On Behalf Of Daniel Gross
Sent: Monday, July 17, 2023 4:19 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx<mailto:
rpg400-l@xxxxxxxxxxxxxxxxxx><mailto:rpg400-l@xxxxxxxxxxxxxxxxxx<mailto:
rpg400-l@xxxxxxxxxxxxxxxxxx>>>
Subject: Re: JSON un-named arrays

Hi Greg,

I don't know, if it helps, but you can walk the whole JSON document with
SQL without problems.

select *
from json_table(
'{...insert.your.json.here...}',
'lax $'
columns (
records integer path '$.records',
fields varchar(1000) path '$.fields',
nested path 'lax $.data[*]'
columns (
trano integer path '$[0]',
ref varchar(50) path '$[1]',
duedate decimal(8, 0) path '$[2]',
name varchar(50) path '$[3]',
amount decimal(10, 2) path '$[4]'
)
)
) as json_doc;

As you can see, the named parts are referenced by ".name", whereas the
"unnamed" JSON elements have no named reference, but only the "[index]".
The nested path reference is the JSON equivalent of an table JOIN.

Of course - if the "data" part of the JSON is dynamic, and you have to
detect the columns be the "fields" element, it can get a but
complicated.

But even then, you can first split the "fields" list into its elements,
an
then construct a dynamic SQL to extract the "data" parts.

So from my POV - SQL is often the easier solution - especially because
you
can test it with Run SQL Scripts until everything is "perfect".

HTH and kind regards
Daniel

Am 17.07.2023<http://17.07.2023><http://17.07.2023<http://17.07.2023>>
um 17:31 schrieb Greg Wilburn <gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx><mailto:
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx
<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>>>:

Typically, I use DATA-INTO and the YAJLINTO parser... But I'm not sure
if that's possible with the JSON below. The array "data" contains an
un-named array of values.

Any suggestions on how handle reading this?
Do I need to use the YAJL "tree-based" method?

{
"records": 4,
"fields": "trano,ref,duedate,name,amount",
"data": [
[
1,
"40061433",
20230719,
"Mudpie",
187.2<http://187.2><
http://187.2<http://187.2>>
],
[
2,
"40061349",
20230719,
"kiddywampus",
156.21<http://156.21><
http://156.21<http://156.21>>
],
[
3,
"40062677",
20230721,
"Chase",
70.8<http://70.8><
http://70.8<http://70.8>>
],
[
4,
"40061703",
20230723,
"Stamford Toys",
110.7<http://110.7><
http://110.7<http://110.7>>
]
]
}
[Logo]<https://www.totalbizfulfillment.com/><https<
https://www.totalbizfulfillment.com/><https>://
www.totalbizfulfillment.com/>><www.totalbizfulfillment.com/>>> Greg
Wilburn
Director of IT
301.895.3792 ext. 1231
301.895.3895 direct
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx
<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx>><mailto:
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx
<mailto:gwilburn@xxxxxxxxxxxxxxxxxxxxxxx><mailto:
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx<mailto:
gwilburn@xxxxxxxxxxxxxxxxxxxxxxx

1 Corporate Dr
Grantsville, MD 21536
www.totalbizfulfillment.com<http://www.totalbizfulfillment.com><
http://www.totalbizfulfillment.com<http://www.totalbizfulfillment.com
<
http://www.totalbizfulfillment.com<http://www.totalbizfulfillment.com><
http://www.totalbizfulfillment.com<http://www.totalbizfulfillment.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx<mailto:
RPG400-L@xxxxxxxxxxxxxxxxxx><mailto:RPG400-L@xxxxxxxxxxxxxxxxxx<mailto:
RPG400-L@xxxxxxxxxxxxxxxxxx>>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx<mailto:
RPG400-L-request@xxxxxxxxxxxxxxxxxx><mailto:
RPG400-L-request@xxxxxxxxxxxxxxxxxx<mailto:
RPG400-L-request@xxxxxxxxxxxxxxxxxx>>
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:
support@xxxxxxxxxxxxxxxxxxxx><mailto:support@xxxxxxxxxxxxxxxxxxxx
<mailto:
support@xxxxxxxxxxxxxxxxxxxx>> for any subscription related questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx<mailto:
RPG400-L@xxxxxxxxxxxxxxxxxx><mailto:RPG400-L@xxxxxxxxxxxxxxxxxx<mailto:
RPG400-L@xxxxxxxxxxxxxxxxxx>>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx<mailto:
RPG400-L-request@xxxxxxxxxxxxxxxxxx><mailto:
RPG400-L-request@xxxxxxxxxxxxxxxxxx<mailto:
RPG400-L-request@xxxxxxxxxxxxxxxxxx>>
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:
support@xxxxxxxxxxxxxxxxxxxx><mailto:support@xxxxxxxxxxxxxxxxxxxx
<mailto:
support@xxxxxxxxxxxxxxxxxxxx>> for any subscription related questions.
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx<mailto:
RPG400-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx<mailto:
RPG400-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:
support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx<mailto:
RPG400-L@xxxxxxxxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx<mailto:
RPG400-L-request@xxxxxxxxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx<mailto:
support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related
questions.

--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related
questions.


--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.


--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.



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