【Query Builder】Handling JSON data

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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/7dc51ea1-d374-ad1c-9af6-fd99966a824e.png)
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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/2d327054-f0e5-9694-c674-e555053964d8.png)
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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/5c2b4055-f617-e11e-56f9-c21646ff2b89.png)
## Selection ##
Those three filter values are entered, this tree view is populated in the `Selection` area.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/20876248-7fdd-5218-5644-576bc5d8b7aa.png)
As you can see, **transactio_info** and **shipping_info** have been extracted from the JSON data.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/d08dbb77-0f34-7361-9476-442be9fe0cd1.png)
## Outputs ##
Now set **transactio_info** and **shipping_info** to the `Outputs` area.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/f02b2cc0-f69f-7e5d-5ee3-02ccba0561a6.png)
## 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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/77466552-48bf-51c1-5915-2aef1bd4b89a.png)
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.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/6bef2aad-3a91-3952-785b-fc3768405b1f.png)
The result is like this.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/58f9fc45-caf2-068e-97d2-ae95fb49d5b1.png)
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**.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/ec726f80-f411-9c85-3ec1-2de193dc61a7.png)
Let's have a check the result. They were extracted fine.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/4da87884-c5bf-40d5-1da7-f24dc2d4e841.png)
With using the same method, try to parse the value in `Transaction Amount` field which is still in JSON format.
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/8b5bbe29-def2-9323-4c79-f466e8e4d2e5.png)
The result is,
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2530698/79607c76-484c-4798-0e3c-33fdad794fb9.png)
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.