Re: json_table(httpgetclob, nested array -- WEB400

...apparently the GitHub link is bad in this reply (cheers Mark for letting me know). Hopefully this works better

https://github.com/fathert/json-rpg-example/blob/master/invoice-extract.sql

Tim.

________________________________
From: WEB400 <web400-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Tim Fathers <X700-IX2J@xxxxxxxxxxx>
Sent: 21 November 2019 15:33
To: Web Enabling the IBM i (AS/400 and iSeries) <web400@xxxxxxxxxxxxxxxxxx>
Subject: Re: [WEB400] json_table(httpgetclob, nested array

Booth,

I'm afraid you are still misunderstanding what "nested" does, it will not get rid of the loop for arrays and nor it is meant to.

Have a look at this SQL, which takes a JSON object containing and array of invoices, each with a nested array of invoice lines, with each line potentially having a nested array or discount codes and extracts the contents into three corresponding database files, which is something similar to a real-world situation.

https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Ffathert%2Fjson-rpg-example%2Fedit%2Fmaster%2Finvoice-extract.sql&data=02%7C01%7C%7C6944fbfe85b141549cff08d76e8fd315%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637099436302748227&sdata=Ha11eTwQK8sKRf9ibv9RClrbs0gIh13fMAX6FtMndag%3D&reserved=0
[https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.githubassets.com%2Fimages%2Fmodules%2Fopen_graph%2Fgithub-logo.png&data=02%7C01%7C%7C6944fbfe85b141549cff08d76e8fd315%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637099436302758238&sdata=Wd5LXOyB6eTUgvQBVfkTFkL5Jn3aUBwplGKPgniOZHc%3D&reserved=0]<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Ffathert%2Fjson-rpg-example%2Fedit%2Fmaster%2Finvoice-extract.sql&data=02%7C01%7C%7C6944fbfe85b141549cff08d76e8fd315%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637099436302758238&sdata=Fn8UBLV86GCSTIXB7O9ZIgpM%2Fuuc2si%2FDYtiJxL0jzE%3D&reserved=0>
Build software better, together<https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Fgithub.com%2Ffathert%2Fjson-rpg-example%2Fedit%2Fmaster%2Finvoice-extract.sql&data=02%7C01%7C%7C6944fbfe85b141549cff08d76e8fd315%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637099436302758238&sdata=Fn8UBLV86GCSTIXB7O9ZIgpM%2Fuuc2si%2FDYtiJxL0jzE%3D&reserved=0>
GitHub is where people build software. More than 40 million people use GitHub to discover, fork, and contribute to over 100 million projects.
github.com

The first table is just somewhere for me to store the JSON test object with the invoices in so isn't really relevant to the rest of the discussion.

The second SQL just iterates over the array of invoices and extracts the JSON fields to the INVOICE_HEADER file. Here you can see the use of "default ... on empty" as another way of avoiding nulls in the result when a missing field is encountered (the email address being optional). The result of this SQL is one row per invoice.

The third SQL uses a nested path in order to iterate over each invoice line of each invoice, in order to extract the invoice line fields to the INVOICE_LINE file. The outer part of the path selector takes the INVOICE_NUMBER of the current invoice (used as our database key to tie everything together later), the inner, nested, part targets the array of invoice lines. The result of this SQL is one row per invoice line (for ALL invoices).

The fourth SQL uses a nested path in order to iterate over each invoice line discount, of each invoice line, of each invoice, in order to extract the discount code to the INVOICE_DISCOUNT file. Again, the outer parts of the path selector take the INVOICE_NUMBER of the current invoice and the LINE_NUMBER of the current line as the keys fields while the inner part targets the array of invoice line discounts. The result of this SQL is one row per invoice line discount (for ALL invoices and ALL invoice lines).

"strict" and "lax" are just used to indicate to the function how tolerant it should be of missing elements and badly formed JSON, so if you target "lax $.email" and it's not there, you won't get an error.

The final SQL just demonstrates how each of the three files above are linke together using the key information.

Tim.

________________________________
From: WEB400 <web400-bounces@xxxxxxxxxxxxxxxxxx> on behalf of Booth Martin <booth@xxxxxxxxxxxx>
Sent: 20 November 2019 19:41
To: Web Enabling the IBM i (AS/400 and iSeries) <web400@xxxxxxxxxxxxxxxxxx>
Subject: Re: [WEB400] json_table(httpgetclob, nested array

I am plowing through this. Stubbornness & obstinence get in my way, so
it takes time.

It does look like your example is the piece that I needed to tie it all
together; thank you. I do have a working 5250 application that consumes
and displays the weather in Moscow, Kiev, and London. It uses the loop,
which I believe "nested" replaces, but gads... worrying abut that is
days away at the rates I make progress.

incidentally, lines of code is not a metric for me, but I need to start
with Hello World levels and build on that or I gloss over the
is-it-plugged-in stupids

On 11/20/2019 3:28 AM, Tim Fathers wrote:

Booth, I'm afraid I'm at a loss then as to what it is you're trying to ask or what the problem is with the solutions you've been provided.

--
This is the Web Enabling the IBM i (AS/400 and iSeries) (WEB400) mailing list
To post a message email: WEB400@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fweb400&data=02%7C01%7C%7C6944fbfe85b141549cff08d76e8fd315%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637099436302758238&sdata=kQQswvQrsbxrBhQgVXd8OhsirDDR60%2Fl6zPdFIQYyG0%3D&reserved=0
or email: WEB400-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fweb400&data=02%7C01%7C%7C6944fbfe85b141549cff08d76e8fd315%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637099436302768243&sdata=aGKRb6W%2BNRhY7egHG5x9I7gFG3FedlzuGw30V%2FoT4pg%3D&reserved=0.

--
This is the Web Enabling the IBM i (AS/400 and iSeries) (WEB400) mailing list
To post a message email: WEB400@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Flists.midrange.com%2Fmailman%2Flistinfo%2Fweb400&data=02%7C01%7C%7C6944fbfe85b141549cff08d76e8fd315%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637099436302768243&sdata=4%2Fzd4uuAd5igdsmoTxmQe6ydfQPdx7Ib9%2F7d2DsyF0Q%3D&reserved=0
or email: WEB400-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://eur02.safelinks.protection.outlook.com/?url=https%3A%2F%2Farchive.midrange.com%2Fweb400&data=02%7C01%7C%7C6944fbfe85b141549cff08d76e8fd315%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637099436302768243&sdata=aGKRb6W%2BNRhY7egHG5x9I7gFG3FedlzuGw30V%2FoT4pg%3D&reserved=0.