RE: Using DATA INTO for JSON, path=, YAJLINTO as parser

Thanks so much, Scott. I think I have it all working now. Only thing I wish I didn’t have to use value_null to ‘0’, but I can live with that. I just created a Scrub procedure to help with that.

I appreciate the explanation of Path Syntax and the number_prefix. I did know about timestamp format, but forgot for a minute when I manually doctored code in sample. I had it defined as a like(xxxx) and free handed it incorrectly.

From: Scott Klement <rpg400-l@xxxxxxxxxxxxxxxx>
Sent: Monday, March 9, 2020 3:37 PM
To: rpg400-l@xxxxxxxxxxxxxxxxxx; Stephen Piland <Stephen@xxxxxxxxxxxxxxxxxxxxxxxxxxxxx>
Subject: Re: Using DATA INTO for JSON, path=, YAJLINTO as parser


Hi Stephen,
On 3/9/2020 2:35 PM, Stephen Piland wrote:


I agree, Scott. I left a lot to the imagination on that. I'd like to 'skip' right to the Invoice element using path= if possible.



[SNIP] removed code sample

I found a few of problems, here.

FIRST PROBLEM: PATH SYNTAX

The path= problem is a little tricky to explain, so bear with me... Your document has a structure like this:

{ "data": { "Invoice": [{ .. fields .. }] } }

The { } denote JSON "objects", which equate to RPG data structures.

The [ ] denote JSON arrays, which equate to the RPG DIM keyword.

So your document begins with an unnamed data structure -- that's the opening { character. One of the subfields inside that structure is "data", which is also a data structure (so a structure inside a structure), and then "Invoice" is an array of data structures inside of "data".

So your path should be: (unnamed element) / data / invoice

However, you are only doing "data" in your path. You can't just assume the outermost data structure (the one without a name) doesn't exist, but that seems to be what you are trying to do. Then, you also left off "invoice" as well. Unfortunately, due to an oversight in the way DATA-INTO was designed, they did not provide for a means of listing an unnamed element inside the path option.

to circumvent this problem, there's a feature in YAJLINTO that allows YAJLINTO to assign a name to the outermost element, even though it's not in the document itself, YAJLINTO will tell RPG that it has a name. This outermost element is referred to as the "document element" (because it's the element the whole document is inside) and the option to YAJLINTO is called "document name". So if you use this option, that gives you a name that can go into the PATH setting.

dcl-c RPGOPTS
const('path=json/data/invoice doc=file case=any');

dcl-c YAJLOPTS const('{ "value_null": "0", "document_name": "json" }');

See the code, above... YAJLOPTS gives the unnamed element the name "json", and the path is now json/data/invoice so that it can be read.

SECOND PROBLEM: TIMESTAMP FORMAT

JSON does not provide for a "timestamp" data type, and there is no standard that I'm aware of for timestamps. They are simply character strings. Therefore, you try to load data directly into an RPG timestamp variable, the data MUST be in the specific format of an RPG timestamp. The data in your document is not, so the timestamp fields will fail.

I recommend changing the "CreatedTimestamp" subfield into a varchar(26) (or whatever makes sense to you -- as long as it's a char or varchar field). Then, if you want to load it into a timestamp field in your program, it'll be up to you to convert the format into RPG's format.

THIRD PROBLEM: USE OF UNNECESSARY OPTIONS

You have coded the allowextra and allowmissing RPG options, and the number_prefix YAJL option. Unless you truly need these options (you don't for the portion of the document you provided) I strongly recommend leaving these off. The allowextra/allowmissing options make the error messages from RPG much less informational, and therefore its harder to troubleshoot problems with these enabled. Remember: With path= you only have to define the elements within the "invoice" array, so it seems nicer to simply code all of the elements into your DS.

The "number_prefix" option is for JSON elements that begin with a number, for example

{ "30-day-outlook": [ xxxx ] }

The problem there is that RPG cannot define a variable that begins with a number like "30", so the number prefix would add a string to the start of the field name, allowing it to match an RPG name. This doesn't appear to be necessary in your document, you don't have any fields that begin with a number, so I would leave this option off.

TIP: USE THE PSDS FOR THE COUNT

This isn't a coding error on your part, but I wanted to include it in case you didn't know. Since the path= option makes "invoice" the outermost element of the document, you cannot use countprefix to get the number of invoices. RPG solves this by providing a field in the Program Status Data Structure (PSDS) that provides a count of the outermost elements in the document (after path has been applied.) It is an INT(20) element at position 372 of the PSDS.

EXAMPLE:

With all of the above in mind, I threw together a quick example. This works for me on the document you provided. I'm aware this is a trimmed down document, so I can't guarantee that it works on the full thing, but I still thought it'd be useful.

**free
ctl-opt option(*srcstmt:*nodebugio);

dcl-ds pgmStat psds;
numElements int(20) pos(372);
end-ds;

dcl-ds Invoice qualified dim(20);
CreatedTimestamp varchar(26);
InvoiceId varchar(50);
FulfillmentDate varchar(26);
SellingLocationId varchar(50);
UpdatedBy varchar(50);
AmountProcessed packed(10:2);
TotalCharges packed(10:2);
InvoiceSubTotal packed(10:2);
ParentOrderId varchar(50);
TaxExemptId varchar(50);
End-ds;

dcl-s x int(10);
dcl-c ifsPathname
const('/home/sklement/piland.json');

dcl-c RPGOPTS
const('path=json/data/invoice doc=file case=any');

dcl-c YAJLOPTS
const('{ "value_null": "0", "document_name": "json" }');

data-into Invoice %DATA(ifsPathname: RPGOPTS)
%PARSER('YAJLINTO': YAJLOPTS);

for x = 1 to numElements;
dsply Invoice(x).InvoiceId;
endfor;

*inlr = *on;