L2-DA Demo Workflow for Sessions 4 - Part I

Advanced Data Access by GET Request

Learning objectives:

• Retrieve data from a RESTful Service through a GET request

• Convert a JSON object into a data table

Workflow description: A fictional customer transaction data set is retrieved from https://fakestoreapi.com and the information of interest is extracted from the returned JSON object.

In the first example, a list of all available products is retrieved by a GET request. The resulting JSON object is processed and the relevant information is organized into a data table.

In the second example, custom URLs are created for multiple GET requests. The JSON objects from these requests are converted to a data table.

Example I: Retrieving information with a GET request, and formatting the resulting JSON object into a data table

1. Retrieving the product data from the GET Request node. The request URL is https://fakestoreapi.com/products

   • If the GET request is executed successfully, Status in the output table should be 200

   • The body column in the output table should contain a JSON object

2. Extracting the information of interest from the JSON object by the JSON Path node.

   • The input column is body

   • Check Remove source column. This removes the body column in the output table.

   • Highlight a value of the following fields, then click on the Add collection query button. The selected fields are added to the Outputs as output columns.

     • id

     • title

     • price

     • category

3. Convert collection cells (ids, titles, prices, and categorys) by an Ungroup node. Each item in the collection is converted to a row. Check on Remove selected collection column so that these collection cells are disregarded in the output table.

4. Remove unnecessary columns (Status & Content Type) with a Column Filter node.

5. Rename the columns by removing "s" at the end with a Column Renamer node.

Example II: Multiple GET requests with custom URLs

1. Creating a table of product categories with a Table Creator node. The column product class contains 4 different categories.

2. Use an Expression node to form URLs used for GET requests. Follow these steps with three separate expressions:

   1. Replace each space (" ") in the product class column with a string "%20"

   2. Replace each apostrophe ("'") in the product class column with a string "%27"

   3. Join the base URL string "https://fakestoreapi.com/products/category/" with product class as processed in the two previous steps. Pass on the resulting string as a new column url.

3. Retrieve product information for different categories by a GET Request node, with the column url as the request URL. Each request receives a JSON object in return, stored in the body column of the corresponding row in the output table.

4. Extracting the information of interest from the JSON object by the JSON Path node. The process is the same as the previous example. Notice that this produces collection cells for each row (or category) separately in the output table.

5. Convert collection cells (ids, titles, prices, and categorys) by an Ungroup node. Each item in the collection is converted to a row. This ungrouping process is performed separately in each product category. Check on Remove selected collection column so that these collection cells are disregarded in the output table.

6. Remove unnecessary columns (product class, url, Status & Content Type) with a Column Filter node.

7. Rename the columns by removing "s" at the end with a Column Renamer node.