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. Some of the categories contain special characters (namely, a space and an apostrophe). Those special characters are converted by a String Manipulation node, following the percent-encoding to be used as part of a URL.

   • Space: represented by %20

   • Apostrophe: represented by %27

3. Forming URLs with a String Manipulation node. This is done by joining the base URL string (https://fakestoreapi.com/products/category/) with the processed category names from the previous step. The resulting URLs are stored in the column url in the output table.

4. 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.

5. 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.

6. 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.

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

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