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 JavaScript's native object data structure, 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 typically has a highly nested tree structure, whereas tabular and GIS data often 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 is better suited to flat tables composed of features and attributes. Therefore, bringing JSON data into FME requires considering its structure.

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 the San Francisco airport (SFO):

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

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, with each unique value becoming a new feature (row) in the resulting table. For example, flattening the above JSON string on the ID attribute would create the following features:

FME offers flexible options for reading, writing, and transforming JSON data into 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: provide a convenient and secure way to store and reuse connection parameters for 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 that extracts a URL from JSON and reads the data from the URL 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.