In the previous article, I sent a Get request to PayPal Rest API, obtained Transaction information, and saved it in a file.
This time, I would like to read and understand the data in JSON format while analyzing the file.
And again, use the Sharperlight scheduler task to do so.
The reason why we use Sharperlight's scheduler task is that the ultimate goal is to automate the flow of retrieving data from the Rest API and synchronizing the data to our database. The data synchronized in this way can be visualized using Sharperlight's powerful reporting function, etc., and used for business purposes.

In creating the analysis task this time, We would like to reduce as much as possible the change work on our side that would otherwise occur due to changes in the schema and the data structure on the PayPal Rest API side, so we would use various methods to achive it, but this time this is a briefe article, so I will omit that area.

Checking Data Structure

If I open the obtained JSON data file, I can see such a structure.

In this partial image, you can see that two records are displayed under transaction_details, and each record has seven _info groups. This time, I will only retrieve the information in the transaction_info group of each record.

What kind of data structure does the transaction_info group have? It looks like this.

I can also see items with child information. Each child's information was like this.

They don't seem to have grandchildren.
The idea is to store this transaction_info information for each record in the [Transaction_Info] table in the database. There are items with child information in the transaction_info information of each record, and I also want to flatten them.
i.e. do something like this;

[paypal_account_id]
[transaction_id]
[transaction_event_code]
[transaction_initiation_date]
[transaction_updated_date]
[transaction_amount_currency_code]
[transaction_amount_value]
[transaction_status]
[ending_balance_currency_code]
[ending_balance_value]
[available_balance_currency_code]
[available_balance_value]
[protection_eligibility]

Loading data

I will create a process to read data in the Sharperlight scheduler task. I could write everything in JavaScript, but this time I'll save some time.
Start Scheduler from the Sharperlight application menu.

General tab

In the General tab, fill in Code, Group, and Description like this.

Actions tab

Go to the Actions tab and add an action with New icon. The first action to add is the Query Combinations action. This action allows me to iterate over each record returned by the defined query. I can do processing like a For loop statement.
Start building a query with the Edit Query button.
In the Filters area, specify System for Products, and Web page tables, CSV, JSON, XML or OData for Tables.
For the URL Link filter, specify a JSON data file path. (Since only one file was specified this time, specify its name directly)
Given a file, the engine will analyze the file and display selectable items in the Selection area.
Specify the root item of the data structure, transaction_details, as the Start Position filter.
Finally, specify transaction_info, which is the target information for this time, in the Outputs area.

Check the output value with the preview button. I can see that only the transaction_info information of each record is output. I can see that the Transaction data obtained from the PayPal Rest API this time has 16 records.

Then it goes through these 16 records one by one. JavaScript is used for that process. Use the output name {%trans_info} to refer to each output record.

Add a JavaScript action using New icon.
Write the code like this in the script field at the bottom. The very first row receives one row of data returned by the query combination. Then convert it to an object and process the columns one by one. Along the way, I will also process child objects. The processed result is displayed on the console each time for debugging.

// Get a record
var output = lib_task.QueryCombinationValueGet('trans_info');
var rec = JSON.parse(output);

// Get columns for the record
var cols = Object.keys(rec);
for (var c in cols){
       if (typeof rec[cols[c]] !== 'object'){
              console.log(cols[c] + " / " + rec[cols[c]]);
              
       }else{       // Processing child Information
              var childRec = rec[cols[c]];
              var childRecCols = Object.keys(childRec);
              for (var cc in childRecCols){
                     console.log(cols[c] + ': ' + childRecCols[cc] + " / " + childRec[childRecCols[cc]]);
              }
       }
}

When executed with the Test button, I was able to get the column names and their values like this. I was able to flatten the child info too

Finally add a Query Combinations End action to close the loop.

After that, I will write this information to the database using the Writeback function of Sharperlight. In this article, I acquired and analyzed transaction data for a limited period of time as an example, but in reality, I will develop such processing to synchronize all data and use that data to create various reports with the powerful reporting functions of Sharperlight.
This process is automated with a scheduler task that incorporates such as delta update function, etc., and only the latest data created after the previous data is acquired and added to the database.

Up to here for this time.

Thank you.