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



Birgitta,

JSON is JavaScript Object Notation and not any other format so this is what
you may expect since
JSON is expected to be imported directly into a Javascript Object with
JSON.parse()

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

Hi Henrik,

this syntax is currently not supported.
You may be able to add a root element, but for the array without "Element
Name" ...
It seems to be pure java script notation instead of JSON

Select x.*
from JSON_TABLE('{"root" :' concat '[
{
"name": "Henrik\"s Address",
"phone": [
"+45 12341234",
"+45 56785678"
],
"email": {
"work": "xxx@xxxxxxxxxxx",
"private": "yyy@xxxxxxxxx"
},
"address": "Byporten",
"country": "Denmark"
},
{
"name": "John",
"country": "England",
"address": "Kings Cross"
}
]' Concat '}'
, '$.root[*]'
Columns(Name VarChar(25) Path 'lax $.name',
eMailWork VarChar(25) Path 'lax $.email.work',
country VarChar(10) path 'lax $.country')) 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 Henrik
Rützou
Sent: Donnerstag, 17. August 2017 06:28
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 wsould like to se the SQL reading this:

[
{
"name": "Henrik\"s Address",
"phone": [
"+45 12341234",
"+45 56785678"
],
"email": {
"work": "xxx@xxxxxxxxxxx",
"private": "yyy@xxxxxxxxx"
},
"address": "Byporten",
"country": "Denmark"
},
{
"name": "John",
"country": "England",
"address": "Kings Cross"
}
]

On Thu, Aug 17, 2017 at 2:09 AM, Jon Paris <jon.paris@xxxxxxxxxxxxxx>
wrote:

How does it work Birgitta in cases where there is no name for the
grouping element in question?

e.g. { "Customers": [
{
"ID": 12345,
"Name": "Paris",
"Street": "Main Street",
"City": "Jasontown",
"State": "CA",
"Zip": "12345"
},
{
"ID": 23456,
"Name": "Rich",
"Street": "South Road",
...

If XML the structure would need to be:

<Customers>
<Customer>
<ID>
....

</Customer>
<Customer>
...

But JSON lets you omit the <Customer> name level and just repeat an
"unnamed" object instead. In fact most cases I have seen where there
is a name it is because it was derived from an XML document.

So how do you code for this in the JSON_TABLE?

Jon Paris

www.partner400.com
www.SystemiDeveloper.com

On Aug 16, 2017, at 10:34 AM, 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




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





As an Amazon Associate we earn from qualifying purchases.

This thread ...

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.