Transform the JSON into a Table for easier manipulation

You need to do quite a bit of filtering and transformation to get the desired result. As you have learned, a good strategy for tackling larger problems is to solve the problem in small steps.

You decide to scope the first step so that the result is the raw JSON data transformed into a Table (a data structure provided by the RPA.Tables library) that is then easy to filter, sort, group, etc.:

producer.robot:

*** Settings ***
Documentation       Inhuman Insurance, Inc. Artificial Intelligence System robot.
...                 Produces traffic data work items.

Library             RPA.HTTP
Library             RPA.JSON
Library             RPA.Tables


*** Tasks ***
Produce traffic data work items
    Download traffic data
    ${traffic_data}=    Load traffic data as table


*** Keywords ***
Download traffic data
    Download
    ...    https://github.com/robocorp/inhuman-insurance-inc/raw/main/RS_198.json
    ...    ${OUTPUT_DIR}${/}traffic.json
    ...    overwrite=True

Load traffic data as table
    ${json}=    Load JSON from file    ${OUTPUT_DIR}${/}traffic.json
    ${table}=    Create Table    ${json}[value]
    RETURN    ${table}

You add a new Load traffic data as table keyword that returns the JSON data in a Table format.

The Load JSON from file keyword provided by the RPA.JSON library returns the file contents (string) in JSON format.
The Create Table keyword from the RPA.Tables library converts the JSON format into a Table.

Tables can be created from lists, for example. Looking at the raw JSON content, you see that the value property points to a list of all the things you will need.

You run the robot and view the log. Your robot has transformed the raw JSON data into a Table with lots of rows:

Created table: Table(columns=['Id', 'IndicatorCode', 'SpatialDimType', 'SpatialDim', 'TimeDimType', 'TimeDim', 'Dim1Type', 'Dim1', 'Dim2Type', 'Dim2', 'Dim3Type', 'Dim3', 'DataSourceDimType', 'DataSourceDim', 'Value', 'NumericValue', 'Low', 'High', 'Comments', 'Date', 'TimeDimensionValue', 'TimeDimensionBegin', 'TimeDimensionEnd'], rows=11640)

Viewing the whole table using a VS Code extensions

The table entry in the log is nice and concise but lacks the actual data.

To view the entire table, you decide to export the Table into a CSV file and view that as a human-readable representation using the Excel Viewer VS Code extension.

You call the super-handy Write table to CSV keyword:

Write Table To Csv    ${traffic_data}    test.csv

After rerunning the robot, you see a test.csv file at the root. Clicking on it opens the raw CSV file. You click on the Open Preview icon at the top-right. A preview of the data is displayed as a table. What a nice way to manually inspect the intermediate results!

View the full data with the Excel Viewer extension

DRY - Don't repeat yourself

Always in search for tidy code, you see that you have broken the Don't repeat yourself principle by duplicating the path to the traffic.json file.

It's not a massive deal at the moment since you have so little code, but it still requires future maintainers to find and change the path in two places if the path needs to be changed.

Little repetition might eventually grow into lots of small repetition and make it slower to maintain your robot or even cause subtle bugs if someone forgets to replace all the instances of the duplicated things.

You decide to store the path of the traffic.json file into a variable and then refer to that variable instead. This way, you remove the duplication:

producer.robot:

*** Settings ***
Documentation       Inhuman Insurance, Inc. Artificial Intelligence System robot.
...                 Produces traffic data work items.

Library             RPA.HTTP
Library             RPA.JSON
Library             RPA.Tables


*** Variables ***
${TRAFFIC_JSON_FILE_PATH}=      ${OUTPUT_DIR}${/}traffic.json


*** Tasks ***
Produce traffic data work items
    Download traffic data
    ${traffic_data}=    Load traffic data as table


*** Keywords ***
Download traffic data
    Download
    ...    https://github.com/robocorp/inhuman-insurance-inc/raw/main/RS_198.json
    ...    ${TRAFFIC_JSON_FILE_PATH}
    ...    overwrite=True

Load traffic data as table
    ${json}=    Load JSON from file    ${TRAFFIC_JSON_FILE_PATH}
    ${table}=    Create Table    ${json}[value]
    RETURN    ${table}