Reading JSON

Introduction

This article covers reading JSON data. FME offers several ways to read JSON data into your workspace. Each is more useful in different scenarios. Some are specifications of JSON and have their own reader formats, including GeoJSON, Esri JSON, and IMDF, summarized in this table:

This article shows how to read JSON directly into your workspace using the JSON reader. It shows two different reader Schema Scan Modes: Automatic and JSON Query. For other scenarios, see the links above.

Step-by-Step Instructions

Part 1: Inspect the Data

We access the FAA Airport Status Web Service API in this tutorial. You can access the API documentation at SwaggerHub. FAA-airport-status-example.json is a sample extract of the data in case the API is unavailable.

If the API URL changes, you can view the attached example data instead, available in the files section of this article.

1. View JSON Data from a URL

Let’s start by taking a look at our source JSON data. The example data contains status information about a US airport, including current visibility and weather conditions. We want to read this live data feed for the San Francisco airport (code SFO), which is available from: https://soa.smext.faa.gov/asws/api/airport/status/SFO

If you open this URL in a browser, you’ll see that you receive an XML response, as this API provides XML by default. If you wanted to receive JSON, you have to request it in an HTTP header.

2. View JSON Data from an HTTP Request with Headers

We can make a proper HTTP request with JSON headers in the browser using SwaggerHub, an API platform used by the FAA to design and document its API.

Go to the API's getAirportStatus operation on SwaggerHub. Note that it may take a while to load. Then: 

Expand the GET /api/airport/status/{airportCode} section

Click the “Try it Out” button to provide parameters to the getAirportStatus operation.

Type “SFO” into the airportCode parameter field. (4) Click the “Execute” button, which will make an API request using the entered parameter. Note that the default value for “Response content type” is “application/json,” meaning that this API request will provide a header requesting JSON as the response.

After clicking “Execute,” you will see some JSON appear under the response body for the HTTP 200 code:

This JSON is the data about SFO’s status we’ll be using in the exercise. It should look something like this:

{
  "Name": "San Francisco Intl",
  "City": "San Francisco",
  "State": "CA",
  "ICAO": "KSFO",
  "IATA": "SFO",
  "SupportedAirport": true,
  "Delay": false,
  "DelayCount": 0,
  "Status": [
    {
      "Reason": "No known delays for this airport"
    }
  ],
  "Weather": {
    "Weather": [
      {
        "Temp": [
          "Fair"
        ]
      }
    ],
    "Visibility": [
      10
    ],
    "Meta": [
      {
        "Credit": "NOAA's National Weather Service",
        "Url": "http://weather.gov/",
        "Updated": "Last Updated on Jul 7 2022, 6:56 am PDT"
      }
    ],
    "Temp": [
      "53.0 F (11.7 C)"
    ],
    "Wind": [
      "West at 11.5"
    ]
  }
}

Part 2: The JSON Reader Auto Mode

1. Open FME Workbench and add a JSON Reader

In FME Workbench, start a new workspace. Click Reader > Add Reader. Choose JSON (JavaScript Object Notation) as the Format.

You can supply a path to a local file (an example is available in the Files) or a URL for the Dataset parameter.

This article will assume you are using a URL. Paste the following URL into the Dataset Parameter: https://soa.smext.faa.gov/asws/api/airport/status/SFO 

Although we know this URL returns XML, FME automatically includes the correct headers to request JSON when using the JSON Reader.

Click the Parameters button. Setting JSON reader parameters lets you control how FME converts JSON's nested structure into features. There are two options for Schema Scan Mode: Auto and JSON Query. Auto mode simply takes the JSON parent attributes and converts them into FME attributes, and converts the rest of the nested structure into FME list attributes. JSON Query mode, which we will examine later in the article, lets you write a JSON Query to pick which part of the JSON data structure you wish to use as the top level to define FME attributes.

For now, make sure that Schema Scan Mode is set to Auto.

Click OK twice to add the reader.

3. Run Workspace

Ensure that Feature Caching is enabled, then click the Run button to run the workspace and create a local feature cache of the JSON data.

4. Inspect the JSON Using Data Preview

Click the JSON reader feature type (or its green cache icon) to inspect it in the Data Preview Table View. You should see that the JSON data has been flattened into a table, with all of the top-level JSON attributes becoming FME attributes: Name, City, State, etc.

You will notice that no attributes with children are included, like “Status” and “Weather.” These attributes are still available on FME features, but because JSON is nested, they are read as lists.

Note: if you have not worked with unexposed attributes or lists in FME before, working with JSON might be challenging. You can read Tutorial: Getting Started with List Attributes for a quick introduction to these topics.

5. View Features in the Feature Information Window

To find these unexposed attributes, click on the single feature in the Table View to select it. Then look in the Feature Information Window in Visual Preview. You can see that all attributes with children are stored as lists on the feature. They use dot notation where their children are attributes and FME list bracket notation where there is more than one attribute-value pair in the array:

In the next part of this article, you will learn how the JSON structure becomes FME features.

Part 3: The JSON Reader JSON Query Mode

1. Change JSON Reader Schema Scan Mode to JSON Query

Now let’s try reading in JSON using a JSON Query.

Expand the JSON Reader in the Navigator window, then Parameters, then double-click Schema Scan Mode

2. Create a JSON Query

To construct a JSONQuery:

1. Change Schema Scan Mode to JSON Query.

2. In the table under Feature Type, type "Weather.”

   

3. Then click in the space under Query, and then click on the ellipses button. This button opens the JSON data’s nested structure, which you can use to build the query.

4. Click on the arrows that contain the nested list objects. Select Weather from the list; the Query will then display json["Weather"].

   

5. Click OK. You may have to confirm the Query by clicking any blank space in the dialog for the OK button to become enabled. The parameters should look as follows:

   

3. Run Workspace

Run the workspace and then inspect the new data cache. The Table View reports that the data has no schema, but the Feature Information Window shows the list of attributes. Only the child data of weather and not the parent data, such as Airport, City, etc., are included:

Data Attribution

The data used in this article comes from the Federal Aviation Administration’s Airport Status Web Service (ASWS) API, which is licensed under Creative Commons 0 (CC0).