RE: Parsing JSON data into a data structure without knowing variable's names -- RPG400-L

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