Tutorial: Getting Started with JSON

Introduction

This tutorial series provides an overview of reading, writing, and transforming JSON data using FME.

JSON, or JavaScript Object Notation, is a file format that follows an open standard and has values that can be an object (attribute-value pairs such as { “a” : 5 }), an array (e.g. [1,2,3,4]), or a primitive (a string or number). It is widely used as a web exchange and messaging format. While it is popular on the web largely because it is the native object data structure in JavaScript, it is a language-independent data format.

FME Approach to JSON

A challenge with JSON data translation to/from tabular or GIS data is the fundamental difference in data modeling. JSON usually has a highly-nested tree structure, while tabular and GIS data usually use a relational database model composed of related tables. It may take several tables to model a given JSON object in a relational system.

FME can work with nested data, but its fundamental design lends itself more to working with flat tables composed of features and attributes. Therefore, bringing JSON data into FME requires considering the structure of JSON.

Terminology for data structures in FME, relational databases, and JSON

Consider this example of nested JSON data representing a point of geometry representing the location of San Francisco airport (SFO):

{
 "name" : "JSONFeature",
 "type" : "FeatureCollection",
 "features" : [
    {
      "ID" : "1001",
      "geometry" : {
        "type" : "Point",
        "coordinates" : [ -122.4194155, 37.7749295 ]
      },
      "properties" : {
        "name" : "SFO",
        "type" : "Airport"
      }
    }
 ]
}

JSON object example

If you are reading in JSON or composing a JSON message in FME, you might choose to store an entire JSON string as an attribute value:

{

"name" : "JSONFeature",

"type" : "FeatureCollection",

"features" : [

{

"ID" : "1001",

"geometry" : {

"type" : "Point",

"coordinates" : [ -122.4194155, 37.7749295 ]

},

"properties" : {

"name" : "SFO",

"type" : "Airport"

}

}

]

}

If you planned to translate a JSON string into a relational database format or table, you could instead choose to “flatten” the JSON string into a table of FME features and attributes. Creating such a flattened JSON requires choosing an attribute from which to generate features, the unique values of which will each be a new feature, or row, in the resulting table. For example, flattening the above JSON string on the ID attribute would create the following features:

JSON object example represented in a table

FME offers flexible options for reading, writing, and transforming JSON data to get it in exactly the shape you need for your data integration workflow.
 

Terminology

• API: Application Programming Interface
• JSON: JavaScript Object Notation
• Parse: Break down code (or a sentence) into parts
• Web service connections: Web service connections provide a convenient and secure way to store and reuse connection parameters to a web service.
   

Articles

1. Reading JSON

Learn how to read JSON data directly into your workspace using the JSON reader.

2. Extracting Location from JSON

Learn how to extract coordinate information from a nested JSON structure and use it to generate FME geometry.

3. Advanced JSON Reading

Learn about more advanced JSON reading techniques, with an example of a multi-step operation where a URL is extracted from JSON and the data provided by the URL is read into the workspace, using the UK Environment Agency Real Time flood-monitoring API.

4. Writing JSON

Learn about the options FME provides for writing to JSON and see a basic example using the JSON writer.

5. Writing JSON with the JSONTemplater

Learn how to write custom nested JSON with FME using the JSONTemplater transformer.

6. Transforming JSON using the JSONExtractor, JSONFlattener, and JSONFragmenter

Learn the use-cases for FME’s most popular JSON transformers and how to use them.