The earlier exercises made a request to the Dublin Bikes API and returned a JSON response containing information about a particular bike station. In the earlier exercise we viewed the response as a single object attached to a feature, this exercise will extend this and show how the JSON transformers can be used to extract the information from the JSON object and turn it into features that can be used within an FME workflow. The final part of the exercise will take this information and produce a simple HTML report using the information from the web service.
1) Open Previous Workspace
Open FME Desktop and select Open and load the Dublin Bike HTTPCaller Workspace we created in the earlier JSON exercise. This can be downloaded here if required.
2) Review Workspace
Review the Workspace and run it to the HTTPCaller reviewing the output. The Workspace will call the Dublin Bikes API for the given data range and return a JSON document containing information on the Bike stations and capacity within the response body attribute.
The next steps within this exercise will use the JSON transformers within FME to process this data.
3) Validate JSON
Prior to processing the JSON further we want to check that the JSON is valid otherwise it will cause the following transformers to break. As our API is a trusted source we can be confident that it will be valid however it is good practise to check the response in case of issues or it has become corrupted during transmission.
Add a JSONValidator transformer and connect it to the HTTPCaller transformer.
Open the transformers parameter window and set the JSON Document to the response body attribute.
Run the JSONValidator and confirm that the JSON response we are working with is Valid. The feature should leave via the passed port however if there is any problem the feature will exit the Failed port and either terminate the translation or allow us to pass the feature to our own error reporting/repair workflow.
4) Format JSON
Most systems will return the JSON as a linearised string, as can be seen in the case of our Dublin Bikes API example. Whilst this is more efficient for machines to read it is harder for a human to read and so it is easier to format the data when we are developing.
The JSONFormatter allows us to Pretty Print our JSON output into a format that is easier to work with. Add a JSONFormatter transformer to the workspace and connect it to the JSONValidator.
Open the JSONValidator transformer and set the JSON Document to the Response Body attribute as shown below.
At the bottom of the transformer parameters is the Output Settings section which contains the Result Attribute parameter. This parameter defines the name of the output attribute containing the formatted JSON. By default this is a new attribute leaving the original intact however to make the workflow simpler replace the current value with the value _response_body as shown which will replace the existing attribute contents with a formatted version.
5) Extracting data from the JSON
The first part of the exercise has made a request to the web service, validated and then formatted the response from that service to ensure that the data is valid and will process correctly through the subsequent transformers as well as making it easy to read to help us work with the data in the next steps.
The response from the API has information about the station such as its location and its address returned at the top level of the JSON object, along with an array containing all of the historic information for the time frame we requested.
The first step we will undertake is to extract the fixed information from the response into attributes. To do this add a JSONExtractor transformer and connect it to the JSONFormatter as shown below.
In the JSONExtractor enter the following values in the Target Attribute and JSON Query sections, this will create an attribute on the feature for each entry below and populate it with the relevant JSON Query for each value.
| Target Attribute | JSON Query |
|---|---|
| address | json[0]["address"] |
| latitude | json[0]["latitude"] |
| longitude | json[0]["longitude"] |
| name | json[0]["name"] |
The data we are using is a good example of how an initially simple looking data structure can hide a degree of complexity as the web service returns an array even though it is giving us a single record back. This means that FME needs to process this Array element first before it can read the subsequent elements. To do this in the JSON Query tell FME to read this first element then the element we want to read, this is done by adding brackets containing the element we want to read at the start of the query. As FME starts counting from zero to read the first element of the array we use [0] so are query becomes json[0].
When you are finished the transformer should look like the below,
When you run the transformer you should get the following result displayed in the visual preview window.
| Detective Lizard: Try Again, Fail Again, Fail Better! |
|
FME has a range of JSON transformers as you've seen, but how web services return their JSON varies from service to service and often the hardest part of building a Workspace is understanding how the JSON has been structured to be able to pass in the correct JSON Query within a transformer. Some services will work directly with the JSONFragmenter, some will return a single response but the JSON is structured as an Object that contains many features or some will contain complex nesting. Reading the API documentation, and studying the response in the data inspector will allow you to understand the data and develop a strategy in FME for processing the data. This can be trial and error as you build up your knowledge of the JSON payload and how its accessed in FME, often you learn more about the data by getting it wrong the first time! |
6) Return a Time Series
Now that we have identified the data structure and learnt to work with a payload that contains an array as its root response working with the data is easier.
We can use the JSONFragmenter to take the data attribute and split this into a feature for each value contained within the historic array. Add a JSONFragmenter transformer and connect it to the JSONExtractor as shown.
Open the transformers parameters and set the JSON Attribute to the response body attribute. Then set the JSON Query to the following.
json[0]and lastly under Flattening Parameters set the Flatten Query Result into Attributes to Yes. The transformer settings should look like below.
To get more than one epoch returned from the API we need to make multiple requests specifying the periods we need. For this exercise we can return the data over 24 hrs. To make multiple requests we can start by adding a Cloner transformer after the initial Creator and AttributeManager.
Open the Cloner Parameters and set the Number of Copies to 24.
Having created a feature for each request we need to update the time stamp, to do this we can use the copy number alongside the DateTimeCalculator to increment by 1 hour per feature.
Add a DateTimeCalculator transformer after the cloner as below
In the settings, set the Datetime to the _init attribute, and the Operator to Add. Then set the Hours to _copynum. Lastly set the Result to the _init attribute.
If you run the transformer and review the output you should see that the timestamp now increments by one hour for each feature through a cycle of 24hrs.
The API expects the dates to be presented in a specific format. To ensure they are formatted correctly add a DateTimeConverter transformer.
Set the Datetime Attributes to _init and the Output Format to %Y-%m-%d %H:%M:%S
This will ensure that the timestamps match the format expected by the API.
7) Review Output
Run the JSONFragmenter transformer and review the output. You will notice that the attributes do not appear in the main table window. This is because though we have asked FME to flatten any available attributes on in the JSON element they are not exposed on to the canvas and therefore FME does not show them in the table view.
To see the attributes click on a row in the table and look in the Feature Information window and you will see the additional values that have been added.
Remember the Table View only shows those attributes that are defined on the canvas, to see all the attributes FME knows about for a feature use the Feature Information panel.
8) Expose the Attributes
FME 2020.1 has a new feature to make exposing attributes easier. Prior to this release the attributes would have to be exposed by manually entering the attributes in the Attributes to Expose option within the JSONFragmenter which is laborious and prone to mistakes.
FME 2020.1 leaverages the Feature Caching to identify what attributes are available and make these assessable to the user. Currently only a few transformers support this but more will be added in future releases. The AttributeExposer supports this mode as does the Tester
The AttributeExposer transformer supports this new mode, so add an ** AttributeExposer** and connect to the JSONFragmenter.
Open the transformer parameters dialogue box and in the bottom right select select Import then From Feature Cache.
This will open a new window that will allow you to expose the attributes from the feature cache. Select the following attributes,
| available_bike_stands |
| available_bikes |
| bike_stands |
| time |
as shown here then click Import to add them to the AttributeExposer.
This will add them to the canvas so they can be easily worked with in the remainder of the workspace as seen below.
9) Add Geometry to Feature
Our data has a spatial location so we can add this to our features by using the VertexCreator transformer. Add and connect it to the AttributeExposer and set the X Value to Longitude and the Y Value to Latitude.
10) Create a HTML Report
In order to better view our data we can create a basic HTML report. To do this connect a HTMLReportGenerator transformer to the VertexCreator.
Inside the HTMLReportGenerator we can add a number of different items to the page contents to build up the report as shown below.
This is a suggested style feel free to modify it as you wish
Image:
Set the Attachment Method to Link and the Image URL to the following,
http://www.dublinbikes.ie/misc/index_files/logo_white.jpgand the Alternate Text to Dublin Bike Logo. This is the text that will be displayed if the image logo cannot be displayed.
Header:
Set the Text Value to the following,
@Value(name)
@Value(address)Set Header Level to H1 and Text Alignment to Center.
Separator
Separator Thickness to 1 pixel
Header:
Set the Text Value to the following,
Station Location:Set Header Level to H3 and Text Alignment to Center.
Map (ESRI Leaflet)
Set the Label Attribute to name and Basemap to Dark Grey
Separator
Separator Thickness to 0.5 pixel
Header:
Set the Text Value to the following,
Bike and Stand Availability:Set Header Level to H3 and Text Alignment to Center.
Chart (Line)
Configure the Content Settings for the Chart as follows,
X Axis Label enter Time Period as a text string.
X Tick Label Attribute to the time attribute.
In the Data Series configure the following
| Data Attribute | Colour |
|---|---|
| available_bike_stands | 255,1,7 |
| available_bikes | 33,255,6 |
Separator
Separator Thickness to 0.5 pixel
Table
Finally in the table settings configure the Table Style to Striped and then configure the Column Settings as below
| Column Contents | Column Name |
|---|---|
| _init | Time |
| bike_stands | Station Stands |
| available_bikes | Available Bikes |
| available_bike_stands | Free Stands |
11) Add HTMLLayouter
To better format the output use a HTMLLayouter configured as below.
12) Add HTML Writer
To view the HTML report add a HTML writer and connect its feature type to the HTMLLayouter transformer.
Run the Workspace and view the output.
| CONGRATULATIONS |
|
The task above goes through the different JSON transformers to validate, format and then convert the JSON objects into features that FME can use in the remainder of the workspace. The nature of the JSON returned by the web service will determine those transformers that are required for any given web service. The transformers used in the exercise are typical of the sorts of data manipulations required and using these it should be possible to process a valid JSON response from any web service. |