× 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. August 2017

Re: Parsing JSON data into a data structure without knowing variable's names

Thanks Birgitta,
I am sure that would work...
except that I made my column that contains the JSON as VARCHAR.

( " 33 -- The resulting length of a concatenation operation involving a

varying-length field exceeded the maximum allowed length for the result
type." )



On Thu, Aug 17, 2017 at 12:26 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

So your JSON document is split over multiple rows?
Try to concantenate all rows to a single string with the LISTAGG function
in your Sub-Select.

Select ListAgg(Trim(YourJSONColumn))
from YourJSONTable
Where ...


Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
„Train people well enough so they can leave, treat them well enough so
they don't want to.“ (Richard Branson)


-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Gerald
Magnuson
Sent: Mittwoch, 16. August 2017 20:24
To: RPG programming on the IBM i (AS/400 and iSeries) <
rpg400-l@xxxxxxxxxxxx>
Subject: Re: Parsing JSON data into a data structure without knowing
variable's names

that works (it properly parses my json)... it returns only the first row
though...
how do I return all row's.... or structure this to loop via cursor?



On Wed, Aug 16, 2017 at 12:34 PM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

Hi Gerald,

Just include an Sub-Select that returns your JSON document from your
table

Select x.*
from JSON_TABLE(
(Select YourJSONColumn
from YourJSONTable
Where ....
Fetch First Row Only),
'$.Starting/Point'
Columns(....)) as x;

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them and keeping them!"
„Train people well enough so they can leave, treat them well enough so
they don't want to.“ (Richard Branson)


-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
Gerald Magnuson
Sent: Mittwoch, 16. August 2017 17:04
To: RPG programming on the IBM i (AS/400 and iSeries) <
rpg400-l@xxxxxxxxxxxx>
Subject: Re: Parsing JSON data into a data structure without knowing
variable's names

I have a db2 table with a column that contains JSON...
how do I describe my "from" when it is a column within a table I am
referencing (to extract the json data)?



On Wed, Aug 16, 2017 at 8:43 AM, Charles Wilt <charles.wilt@xxxxxxxxx>
wrote:

Oh 7.2 got JSON_TABLE() in October 2016 also...

Charles

On Wed, Aug 16, 2017 at 7:42 AM, Charles Wilt
<charles.wilt@xxxxxxxxx>
wrote:

RPG's not the right choice for this...

Unless you happen to be on 7.3 and can build a dynamic SQL
statement that uses the JSON_TABLE() function.

I think PHP or Python would be able to do this pretty easy.

Charles

On Wed, Aug 16, 2017 at 5:09 AM, Maria Lucia Stoppa
<mlstoppa@xxxxxxxxx>
wrote:

Henrik, it doesn't seem to me it works the way I need it to work,
because
in your specification "%addr(cust)" cust is the name of the final
variable
that must be loaded with content retrieved from json, while in my
case
it
is exactly what I don't know; instead I have a variable namevar
which contains "cust" read from data structure.

2017-08-15 13:18 GMT+02:00 Maria Lucia Stoppa <mlstoppa@xxxxxxxxx>:

I see. Ok I will try it. Thank you Il 14/Ago/2017 14:05,
"Henrik Rützou" <hr@xxxxxxxxxxxx> ha scritto:

This is where it gets a little tricky.

Let assume you have a name field frmpos 11 topos 41 in the DS
named
cust
the storage
address for name will start at addr = %addr(cust)+frmpos-1

Then you can move the JSON data byte to byte into the DS in a
for-loop.
The
reason that
you have to move it byte for byte and not as a block is that
you may overwrite other data if the block is bigger than the
result field.

This is rather simple for a alpha field but rather complicated
for
numeric
and even packed
numeric fields.

On Mon, Aug 14, 2017 at 1:39 PM, Maria Lucia Stoppa <
mlstoppa@xxxxxxxxx>
wrote:

Sure, there is something I am missing.

I make use of API often, so in my case at a certain point
there
might
be an
RPG variable rfNAME evaluated through API (or at least other
two *
methods
I can think of) whose content is "NAME" and the JSON current
node
name
is
"NAME" whose paired value is "Maria Lucia"; then comparing
rfNAME
and
JSON
nodename I finally know I have found the right JSON node for
a
variable
belonging to my data structure: how would you write an RPG
specification
which assign "Maria Lucia" to RPG variable NAME?

2017-08-14 10:35 GMT+02:00 Henrik Rützou <hr@xxxxxxxxxxxx>:

Here is what you need and it will give you alle the info
you
need:

https://www.ibm.com/support/knowledgecenter/en/ssw_i5_54/
apis/quslfld.htm

On Mon, Aug 14, 2017 at 9:47 AM, Maria Lucia Stoppa <
mlstoppa@xxxxxxxxx>
wrote:

If the variable that contains the name is untied to its
pointer
how
can
it
be used to load data in the right memory position?
Il 14/Ago/2017 09:32, "Henrik Rützou" <hr@xxxxxxxxxxxx>
ha
scritto:

Maria,

if you define the datasctructure externally there are
API's
that
can
read
the field names in the DS within your program and then
you can use JSON-"find".

There may however be a problem since JSON names are
case
sensitive
and
DS
names probably
are in upper case.


On Mon, Aug 14, 2017 at 9:12 AM, Maria Lucia Stoppa <
mlstoppa@xxxxxxxxx>
wrote:

Hi everybody

Thank you all for this interesting thread. Just let
me sum
up
all I
read
in
it.

I would leave Javascript outside this discussion,
because I
have to
do
this
job in pure RPG ILE without the help of WDSC or
other
develop
environment.
Anyway, JSON format being pretty much similar to
Javascript
object
description is done on purpose as it makes data
immediately
available
in
a
Javascript object just after being read.

I think all of you got the point of my request,
which is to
programmatic
read JSON data to be loaded into a data structure;
anyway,
from
all
your
answers it seems impossible to match a JSON pair
name/value
with a
DS
variable/value and that leads to build a couple of
programs:
a
generator
code which knowing the data structure to be loaded
can
write
appropriate
specifications which in turn know what to read.

Because the obstacle here is how to make a JSON node
identifying
itself
in
a given data structure, as Jon clearly pointed out.

In my case, types and lengths of variables against
those of
JSON
values
are
not an issue, because they must match otherwise an
error
should
arise.

Let me try to reverse the approach: my routine
starts
looping
through
the
data structure definition, let's say by reading DS D
specifications
or
whatever is needed as this is a different topic,
then the
generic
routine
can look for a specific information into the JSON.
But
how?
Given
my
ignorance, I guess variables' names are converted
into
pointers
at
compile
time, but is there a way to reverse the process so
that to
obtain a
variable which contains the name of a variable given
a
pointer?


2017-08-14 3:55 GMT+02:00 Jon Paris <
jon.paris@xxxxxxxxxxxxxx>:

You missed my point Brad - which was that
YAJL_OBJECT_LOOP
will
loop
through anything. The rest of it was just showing
how the
generic
object
can be compared to find its identity.

But I agree in as much as one always needs to know
something
in a
business
processing environment. Unless you are simply
writing a
utility
to
report
(for example) on what it finds.


Jon Paris

www.partner400.com www.SystemiDeveloper.com

On Aug 13, 2017, at 12:22 PM, Bradley Stone <
bvstone@xxxxxxxxx

wrote:

On Sun, Aug 13, 2017 at 1:53 PM, Jon Paris <
jon.paris@xxxxxxxxxxxxxx

wrote:
Not necessary to know the structure Nathan as
you can
query
what
kind
of node you are dealing with.

A straight object loop can also be used - for
example:

Dow YAJL_OBJECT_LOOP( customerNode: i:
key:
node
);
Select;
When key = 'ID';
customer(c).id =
YAJL_GET_NUMBER( node
);
When key = 'Name';
customer(c).name =
YAJL_GET_STRING(
node
);

In this instance I only need certain fields and
the
names
are
known
-
but the YAJL_OBJECT_LOOP itself dd not require
that I
know
the
names
or
types of the objects.


How is knowing the names, data types and
container not
knowing
the
structure?

You know you're in a customer array that
contains at
least a
numeric
ID and character name. I'd say that's at least
knowing
the
structure
at least partially, if not wholly.

As I said earlier I think code gen is the way
to go -
but
the
JSON
structure can be dynamically determined just as
XML-SAX
allows
you
to
do
with XML.


Yes, that makes sense, but once you do the
program
"knows"
the
structure and is built appropriately around thus
said
structure.

Bradley V. Stone www.bvstools.com MAILTOOL
Benefit #2: The ability to specify a "From"
and/or
"Reply
To"
email address!
--
This is the RPG programming on the IBM i (AS/400
and
iSeries)
(RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list
options,
visit: http://lists.midrange.com/mail
man/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before
posting, please take a moment to review the
archives
at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any
subscription
related
questions.

Help support midrange.com by shopping at
amazon.com
with
our
affiliate
link: http://amzn.to/2dEadiD

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

Please contact support@xxxxxxxxxxxx for any
subscription
related
questions.

Help support midrange.com by shopping at
amazon.com with
our
affiliate
link: http://amzn.to/2dEadiD




--

Maria Lucia Stoppa
mlstoppa@xxxxxxxxx
--
This is the RPG programming on the IBM i (AS/400 and
iSeries)
(RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before
posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any
subscription
related
questions.

Help support midrange.com by shopping at amazon.com
with
our
affiliate
link: http://amzn.to/2dEadiD




--
Regards,
Henrik Rützou

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

Please contact support@xxxxxxxxxxxx for any
subscription
related
questions.

Help support midrange.com by shopping at amazon.com
with our
affiliate
link: http://amzn.to/2dEadiD
--
This is the RPG programming on the IBM i (AS/400 and
iSeries)
(RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx To
subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting,
please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription
related
questions.

Help support midrange.com by shopping at amazon.com with
our
affiliate
link: http://amzn.to/2dEadiD




--
Regards,
Henrik Rützou

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

Please contact support@xxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with
our
affiliate
link: http://amzn.to/2dEadiD




--

Maria Lucia Stoppa
mlstoppa@xxxxxxxxx
--
This is the RPG programming on the IBM i (AS/400 and
iSeries)
(RPG400-L)
mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting,
please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link: http://amzn.to/2dEadiD




--
Regards,
Henrik Rützou

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

Please contact support@xxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link: http://amzn.to/2dEadiD




--

Maria Lucia Stoppa
mlstoppa@xxxxxxxxx
--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email:
RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list
options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please
take a moment to review the archives at
http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link: http://amzn.to/2dEadiD



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

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our
affiliate
link: http://amzn.to/2dEadiD
--
This is the RPG programming on the IBM i (AS/400 and iSeries)
(RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

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

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L)
mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives at
http://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

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

Please contact support@xxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: http://amzn.to/2dEadiD

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.