Introduction
In the previous article, Writing JSON, we looked at the different ways to write JSON using FME. In this article, we’ll look in detail at how to write nested JSON using the JSONTemplater and the Text File writer. This workflow will allow us to write the nested data mentioned in the final step of the previous article.
This use case is a bit more advanced, but it is necessary if you want to write nested JSON data. The basic pattern is to read in data (in this case, JSON with no geometry, but it could be any data in FME), use a JSONTemplater to create FME attributes containing JSON with nested structures, and then write the JSON attributes out to a text file. We are using a Text File writer instead of a JSON writer because, in FME, any time you want to write out the contents of an attribute directly, you use the Text File writer.
The JSONTemplater uses a template approach to writing nested JSON (very similar to the XMLTemplater). A template represents the structure of the data, with functions such as fme:get-attribute, fme:get-json-attributes, or fme:process-features used within the template to build the JSON structure from FME features. Using sub-templates lets one create a document with a root element and multiple child elements.
Step-by-step Instructions
1. Start FME Workbench and Generate Workspace
Start FME Workbench and click Generate Workspace. Fill in the Generate Workspace dialog as follows:
- Reader
- Format: JSON (JavaScript Object Notation)
- Dataset: PublicArt.json (download and use local path)
- Writer
- Format: Text File
-
Dataset: ...\Output\PublicArtNested.json (wherever you wish)
- You may have to switch the file type to All Files (*) for the .json extension to work.
2. Inspect the Source Data in Data Preview
Run the workspace and inspect the source dataset
You can see that each of the 185 features is an art installation in Vancouver. Although the data includes longitude and latitude values, we will not be creating geometry in this example. The goal is to transform this JSON from a large array with Neighborhood as an attribute to a nested structure of key-value pairs where each art installation is nested underneath its respective neighborhood, like so:
{
"Downtown": [{
"Name": "Harbour Centre Parkade",
"Title": "The Belonging Action",
"Longitude": -123.110097741722,
"Latitude": 49.2837806793832
},
{
"Name": "Chinese Cultural Centre",
"Title": "China Gate",
"Longitude": -123.103282272368,
"Latitude": 49.2797561341325
},
...
],
"Strathcona": [{
"Name": "National Works Yard",
"Title": "Roller",
"Longitude": -123.092675,
"Latitude": 49.2736209999959
},
{
"Name": "Jim Green Residence",
"Title": "Entranceway",
"Longitude": -123.095131,
"Latitude": 49.2842699999959
},
...
],
...
}3. Add a Sampler
To build our nested JSON, we will use a JSONTemplater with two sub-templates: one to create a separate array for each neighborhood, and another to contain all the art installations in that neighborhood. To get a separate array for each neighborhood, we need to provide the JSONTemplater with six features, one from each neighborhood. To do this, we’ll use a Sampler transformer. Add a Sampler and connect it to the reader feature type, then set the following parameters:
-
Group Processing: Enable
- Group By: Neighborhood
- Sampling Rate (N):1
- Sampling Type: First N Features
With Feature Caching enabled, click Run To This on your Sampler. You should have six features coming out of the Sampler:Sampled port, one for each neighborhood. These will be provided to one of the JSONTemplater sub-templates.
4. Add a JSONTemplater
Now that we have the neighborhood features, add a JSONTemplater after the Sampler, but do not connect it; we need to create input ports first.
Open the JSONTemplater. First, let’s add our sub-templates. Sub-templates are used to turn FME features into children of the ROOT template, or even other sub-templates.
To add a sub-template, check the Sub Template box and then click the + button in the Sub Template table. In the Port field, rename this sub-template NEIGHBORHOOD. Add another sub-template named ART.
You’ll notice each Template field is red, meaning we must supply a value before we can click OK. Because we want to connect our new sub-template ports to features, start by just typing empty curly braces {} into each Template field. Your dialog should look like this:
5. Connect Features to the JSONTemplater Input Ports
You’ll see that each template (ROOT, NEIGHBORHOOD, and ART) creates an input port on the JSONTemplater. We are supplying six features to ROOT, but currently none to the other ports. Connect the Sampled port on the Sampler to the NEIGHBORHOOD input port. Then, since we want ART to contain all of the art installations, connect the reader feature type to the ART input port. This will provide all 185 features for use in our sub-template.
Now we need to connect features to the Root template. Because we want a single JSON document output from the JSONTemplater, the easiest approach is to use a Creator, which provides a single feature to the Root template. Add a Creator to the canvas and connect it to the JSONTemplater's Root input port.
6. Build the ROOT Template
Now that we have features connected to both our templates, open the JSONTemplater parameters again.
First, let’s set up the ROOT template. This template sets the top level of the JSON hierarchy and, in this case, will execute only once because it receives one feature. Click on the ellipse [...] button in the ROOT Template field to open the ROOT Template Expression dialog. This dialog is similar to the Text Editor and lets us build our Template Expression. Copy and paste the following template, or build it yourself by typing and double-clicking the Sub Templates > SUB on the left to add functions (note the pipes inside the curly braces, {| |} ):
{|
fme:process-features("NEIGHBORHOOD")
|}The fme:process-features(“NEIGHBORHOOD”) function will insert the results of our sub-template NEIGHBORHOOD as items in the array.
For this example, we are using one of the slightly more advanced JSON templating expressions, the pipe |. As specified in the JSONiq documentation, the pipe is a dynamic object construction expression. What it means, in this case, is to create each sub-template result as a separate object. If we didn’t add these pipes, the resulting JSON document would not have the required commas between each neighborhood entry.
7. Build the NEIGHBORHOOD template
This sub-template will create a JSON document for each feature, with the neighborhood name substituted for fme:get-attribute("Neighborhood"). The fme:process-features(“ART”) function will insert the results of our sub-template ART as items in the array. Click on the ellipses [...] button next to the Template field for NEIGHBORHOOD. Copy and paste the following template:
{
fme:get-attribute("Neighborhood") : [
fme:process-features("ART", "Neighborhood", fme:get-attribute("Neighborhood"))
]
}8. Build the ART template
Next, let’s build the ART sub-template. Open the Template Expression for ART. Copy and paste the following template:
{
"Name" : fme:get-attribute("Name"),
"Title" : fme:get-attribute("Title"),
"Latitude" : fme:get-attribute("Latitude"),
"Longitude" : fme:get-attribute("Longitude")
}This template will build an array of art installation data, with each FME feature converted into a JSON object matching this pattern. Click OK.
9. View the Results
Run the workspace and inspect the JSONTemplater's Output port. You should see all the neighborhoods combined into a single JSON document, matching our goal template in the _result attribute. Click the ellipse [...] button next to the cell in the Table View of Visual Preview to view the full value. However, this JSON is quite hard to read as it is not pretty-printed:
Let’s fix that issue.
10. Add a JSONFormatter
Add a JSONFormatter after your JSONTemplater. This transformer will format our JSON document for pretty printing. Open its parameters and set the JSON Document to _result. Then, set the Output Attribute to text_line_data. This attribute name is reserved for use when writing using the Text File writer. Your dialog should look like this:
11. Run Workspace and Inspect Final Results
Connect the JSONFormatter to the Text File writer feature type. Run your workspace and inspect the final results, either in Visual Preview or your text editor of choice. You should see JSON following the goal structure identified at the beginning of the article, something like this (abbreviated):
{
"Downtown": [{
"Name": "Harbour Centre Parkade",
"Title": "The Belonging Action",
"Longitude": -123.110097741722,
"Latitude": 49.2837806793832
},
{
"Name": "Chinese Cultural Centre",
"Title": "China Gate",
"Longitude": -123.103282272368,
"Latitude": 49.2797561341325
},
...
],
"Strathcona": [{
"Name": "National Works Yard",
"Title": "Roller",
"Longitude": -123.092675,
"Latitude": 49.2736209999959
},
{
"Name": "Jim Green Residence",
"Title": "Entranceway",
"Longitude": -123.095131,
"Latitude": 49.2842699999959
},
...
],
...
}Congratulations! You learned how to create custom nested JSON from FME features using the JSONTemplater. Combining sub-templates with the functions available in the Template Expression dialog allows for complex custom JSON results.
Data Attribution
The data used here originates from open data made available by the City of Vancouver, British Columbia. It contains information licensed under the Open Government License - Vancouver.