Sharperlight Query Builder has a capable to query against API Endpoint in some cases.
I will show a simple example in this article this time.
Launch Query Builder from Sharperlight Application Menu.
Filters
For the Mode filter, select Summary Report.
System should be chosen for Product filter.
For the Table filter, select Web Page Table, CSV, JSON, XML or OData from the lookup list.
As soon as the table filter value is chosen, the related filters and also the list of Selection will be populated.
Filters with a red background are filters attached to the selected table value.
The "Header Content" filter was selected from the selection list to use as a filter.
This time, we set values for URL Link, Header Content and Start Position filters.
Use this URL for the URL Link filter. This is a sample URL Query for PayPal API Transactions.
https://api-m.paypal.com/v1/reporting/transactions?start_date=2022-10-01T00:00:00Z&end_date=2022-10-31T23:59:59Z&fields=shipping_info&page_size=100&page=1
Then for the Header Content, set value like this. *This time we assume that we already have a valid Access Token.
Authorization: Bearer SA21AANjR8w_v4H0g5WhPp-Ara9yI38h1nvZ1Mvd3ALzcsd34tPlrfOf5pgPWBEolQxb2aBe2_S06zLDM3db2I51R742zAI3orxvYQE
The final, this is for Start Position filter. This says which part of JSON data should be extracted.
transaction_details
i.e. This part in the JSON data returned from API.
Selection
Those three filter values are entered, this tree view is populated in the Selection area.
As you can see, transactio_info and shipping_info have been extracted from the JSON data.
Outputs
Now set transactio_info and shipping_info to the Outputs area.
Preview
Run the query with the Preview button. Records are returned like this. The contents of these two fields are still in JSON format and need to be parsed more.
This time, I will extract values from them one by one with Expressions.
Go back to Query, and add a new expression from the right-click menu.
Extract the value for paypal_account_id from transaction_info in JSON with JSONNamedValue() function.
The result is like this.
Try to extract values for two more fields with the same method.
transaction_info is referenced by these expressions but should not be visible, so we hide it and do the same for shipping_info.
Let's have a check the result. They were extracted fine.
With using the same method, try to parse the value in Transaction Amount field which is still in JSON format.
The result is,
Query Builder has a function to handle JSON data obtained by query like this.
Please try to use it according to various purposes.
Thank you for reading this article.