Transforming JSON using the JSONExtractor, JSONFlattener, and JSONFragmenter

Introduction

Within FME, there are many ways to manipulate JSON to extract the desired data. In this article, we will cover three transformers: the JSONFlattener, JSONExtractor, and JSONFragmenter. In the first example, we will extract weather information from an Environment Canada JSON API feed using the JSONFlattener and JSONExtractor. In the second example, we will take a JSON file and fragment it into individual attributes, each containing multiple records, using the JSONFragmenter.

Step-by-Step Instructions

Part 1: Reading a Simple Message 

We will read a JSON message from the Environment Canada API using the HTTPCaller and then extract the weather status. This example demonstrates two methods for creating attributes from a JSON message. First, we will use the JSONFlattener to flatten the JSON, thereby exposing all the attributes. Second, we will use the JSONExtractor to extract a particular part of the JSON using a query.

1. Start FME Workbench

Open FME Workbench and start a blank workspace

• Add an HTTPCaller to the canvas to start your workflow.
• Add a Creator transformer to the canvas, then add an HTTPCaller transformer and connect the Creator to the HTTPCaller.  The Creator will trigger the HTTPCaller to get information from the URL.

As of FME 2025.2, many transformers have been updated to not require input from the creator transformer. For a list of all the transformers with this improvement, please see Transformers with an Optional Input Port

Double-click on the HTTPCaller transformer to open the parameters and set the following:

• Request URL: https://api.weather.gc.ca/collections/citypageweather-realtime/items/bc-85?lang=en
• HTTP Method: GET

You can test the API call by clicking the Send Test Request button, which will open another window where you can see the results of a test call. This is handy for checking if the API is online and if your request URL and parameters are valid.

This API call is for Victoria (bc-85); if you would like to follow this example with your own city, look up the city code and replace the string at the end of the URL. Your parameters should look like this:

2. Add an AttributeFileReader

In the event the API is down, add an AttributeFileReader to the Rejected port of the HTTPCaller. This will read the local VictoriaWeather.json file provided in the Files section of this article.

Open the AttributeFileReader parameters, set the following: 

• Source Filename: VictoriaWeather.json
  • Click on the ellipsis to navigate to the file location on your computer
• Source File Character Encoding: System Default (fme-system)
• File Contents: _file_contents

Click OK to accept the parameters.

3. Select the Attributes to Keep

We are only interested in keeping the _response_body attribute from the HTTPCaller or AttributeFileReader. Add an AttributeKeeper transformer to the HTTPCaller Output port and AttributeFileReader Output port. 

In the parameters, set:

• Attributes to Keep: _response_body 

Click OK.

Your workspace should look something like this:

4. Create Attributes with the JSONFlattener

To access individual attributes, e.g., weather status and temperature, we need to flatten the JSON. Add a JSONFlattener transformer and connect it to the AttributeKeeper.

In the parameters, set:

• JSON Document: _response_body

Click OK.

Run the workspace with Data Caching enabled and select the single feature in Data Preview. The Record Information Window indicates that _response_body contains many unexposed attributes (the number of attributes varies by response), which contain all the necessary information.

The JSONFlattener workflow helps turn all JSON values into FME attributes. The challenge then becomes choosing which attributes to expose using an AttributeExposer.

5. Extract the JSON to Find the Weather Condition and Temperature

If we are interested in a single element or property, we can extract it directly into an attribute using the JSONExtractor, rather than flattening the JSON with the JSONFlattener. 

For this exercise, we want to find the weather conditions and temperature for today. From the record information in the previous step, we can see that the property properties.currentConditions.condition.en contains the value for the weather condition and further down properties.currentConditions.temperature.value.en has the temperature value.

For strategies on how to build JSON queries, please see the next section

We will need to extract the parent element and expose the child element. Connect a JSONExtractor transformer to the AttributeKeeper. In the JSONExtractor parameters, set the following: 

• Input Source: JSON Document 
• JSON Document: _response_body 
• Extract Queries:
  • Target Attribute: Weather
    • JSON Query: json["properties"]["currentConditions"]["condition"]["en"]
  • Target Attribute: Temperature
    • JSON Query: json["properties"]["currentConditions"]["temperature"]["value"]["en"]

6. Inspect the Data

Run the entire workspace, then click the green eye icon on JSONExtractor to see the results. In the Data Preview, you should see the _response_body, Weather, and Temperature attributes.

Strategies for Building JSON Queries for JSON Transformers

1. Data Preview

It can be challenging to find the query to access the weather information. There are two main options here: first, you can take a closer look at the JSON structure using Visual Preview and build your query based on that:

"properties": {
"currentConditions": {
"condition": {
"en": "Mostly Cloudy",
"fr": "Généralement nuageux"
},
"dewpoint": {
"qaValue": {
"en": 100,
"fr": 100
},

To view the formatted JSON, click the data cache for the HTTPCaller, then click the ellipsis in the Data Preview under _response_body. In the text window that opens, click on the ABCXYZ drop-down to select JSON. Toggle on the { }, word wrap, and formatting buttons to see all the syntax highlighting

2. FeatureReader

The second option is to temporarily add a FeatureReader to the canvas and read the JSON from the URL. Click on the parameters and set the Schema Scan Mode to ‘JSON Query’. Then, under Query, click the ellipses [...] to expose the JSON tree. In the tree, expand 'JSON', then expand 'Properties', ‘currentConditions’ and then ‘condition’ to finally select ‘en’. 

Using the FeatureReader lets you view the JSON structure visually without having to navigate lengthy text.

Copy the JSON query from the Selected Item box; it's the same one we entered into the JSONExtractor. After you’ve copied this query, you can delete the FeatureReader:

Part 2: Extracting a Complex Message 

In the previous example, we read in JSON and examined the output using a JSONFlattener and a JSONExtractor. In this example, we will use a JSONFragmenter to work with more nested JSON. We will extract latitude and longitude values from JSON to create points.

1. Read in the JSON Using the Text File Reader

In a blank workspace, or in another section of the previous workspace, add a reader and set the following parameters:

• Format: Text File
• Dataset: vantraffic.json
  • Click on the ellipsis to navigate to the file location on your computer and change file types to All Files (*)

Click on Parameters

Under Reader Parameters set:

• Read Whole File at Once: Yes

Click OK twice to add the reader to the canvas

The reason we are reading the JSON data with a Text File reader rather than a JSON reader is that we want the JSON contained in a single attribute called text_line_data. If we read in the JSON using the JSON reader, each line will be a separate record contained within its own attribute, including the brackets.

2. Add a JSONFragmenter to Break the JSON Down into Individual Attributes

Connect a JSONFragmenter to the text_line reader feature type. In the parameters, set the following:

• JSON Attribute: text_line_data
• JSON Query: json["features"][*]
• Flatten Query Results into Attributes: Yes
• Recursively Flatten Objects/Arrays: Yes

Click OK to accept the settings. 

Alternatively, you can construct this query using the methods outlined in the second part of the article. 

3. Expose Attributes of Interest

There are multiple ways to accomplish this. If you know the attribute names you need to expose, you can add those to the previous transformer in the Attributes to Expose field, or you can use an AttributeExposer transformer.

Add an AttributeExposer transformer and connect it to the JSONFragmenter. In the parameters, you can manually enter the attributes or import the attributes from the data cache. If you haven't run the workspace yet, do so now to create the data cache.

Within the AttributeExposer parameters, click on the Import button and select ‘From Data Cache…’, and a window with all the attributes will be displayed. Check the attributes you want to use, then click Import.

After you click Import, you can see each of the attributes you selected in the AttributeExposer Parameters. 

Click OK to accept the settings. 

4. Create Points

Add a VertexCreator transformer after the AttributeExposer. Set the geometry coordinates using the array values 0 and 1 for latitude and longitude.

You can copy and paste the values from below, or to find the list element yourself, click on the drop-down arrow next to the X value field and select 'Attribute Value, geometry'. coordinates{}..., and then in the List Element Selection window, you can type in the specific element you want. 

In the parameters, set the following:

• Mode: Add Point
• X Value: geometry.coordinates{0}
• Y Value: geometry.coordinates{1}

Click OK.

5. Project the Coordinates into LL84

Add a Reprojector transformer and connect it to the VertexCreator. In the parameters, set the following:

• Destination Coordinate System: LL84

Click OK.

6. Inspect the Results

Run the workspace with data caching enabled. Click on the Reprojector transformer’s data cache icon and see the results in the Data Preview window.

Data Attribution

Part 1: The data used here originates from open data made available by Environment Canada

Part 2: The data used here originates from open data made available by the Government of British Columbia - Drive BC.