Example: Convert JSON to CSV

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Data Sources and Targets > JSON >

Example: Convert JSON to CSV

This example shows you how to read data from a JSON file and convert it to a comma-separated text file with MapForce. You will also learn how to map data from additional properties that might be present in JSON instance files like the one illustrated in this example. Additional properties refers to data that is present in the JSON instance file even though it is not defined by the schema. As a rule, you should avoid relying on additional properties and instead define all the properties in your JSON schema. However, in the event that the JSON instance contains additional properties, you can use the technique illustrated in this example.

 

This example is accompanied by a demo mapping file available at the following path: <Documents>\Altova\MapForce2019\MapForceExamples\Tutorial\ReadJSON.mfd. If you follow the steps below, you will learn how to create such a mapping file from scratch.

 

The source JSON file contains people records that must be converted to CSV. Notice the highlighted text: the first and the second person have an additional property that is not defined in the schema, namely birthday.

 

[

 {

         "name": "Alethia Alonso",

         "email": "altethia@example.com",

         "age": 35,

         "birthday": "4 July"

 }, {

         "name": "Klaus Mauer",

         "email": "klaus@example.com",

         "age": 57,

         "birthday": "31 August"

 }, {

         "name": "Natsuo Shinohara",

         "email": "natsuo@example.com",

         "age": 29

 }        

]

People.json

The JSON schema used in the example is represented below. As the $schema keyword indicates, the schema validates JSON instances with respect to Draft 04 JSON Schema. It describes a people array which consists of multiple person objects. The people object must contain at least one person object to be valid. Each person object has a name, age, and email address as properties. Note that name and email are of type string, while age is of type integer. Also, the name and email properties are required, while age is optional.

 

{

 "$schema": "http://json-schema.org/draft-04/schema#",

 "title": "people",

 "type": "array",

 "items": {

 "title": "person",  

 "type": "object",

   "required": [

     "name",

     "email"

   ],  

   "properties": {

     "name": {

       "type": "string"

     },

     "email": {

       "type": "string",

       "format": "email"

     },

     "age": {

       "type": "integer"

     }

   }

 },

 "minItems": 1

}

People.schema.json

Importantly, the JSON schema above does not have an additionalProperties property for the person object. When not present in the JSON schema, the additionalProperties property has the default value of true, which means that the object in the JSON instance can have as many additional properties as required, and still be valid. Therefore, a JSON instance file like People.json will validate successfully against this schema.

 

To achieve the mapping goals of this example, take the steps below.

 

Step 1: Add the source JSON file to the mapping

1.On the Insert menu, click JSON Schema/File and browse for the following file: <Documents>\Altova\MapForce2019\MapForceExamples\Tutorial\People.schema.json. When prompted to specify an instance, select the People.json file in the same folder. At this point, the MapForce component looks as follows:

mf_json_01

The structure of the MapForce component resembles that of the JSON file itself, with the exception of property (additional) node. This node indicates that the additionalProperties property of the person object is either missing or set to true in the schema. This means that the schema can contain custom additional properties, so MapForce displays the node in case you want to map from any additional properties of the object (the next steps show how to do this).

 

To change the source JSON file or the JSON schema file at any time, double-click the title bar of the JSON component and tweak the settings, see JSON Component Settings.

 

Step 2. Add the target CSV to the mapping

1.On the Insert menu, click Text File.

mf_json_02

2.Select Use simple processing for standard CSV, and then click Continue.

mf_json_03

3.Click Append Field several times to add four CSV fields as shown above, and then click OK.

 

For more information about text components, see CSV and Text Files.

 

Step 3: Draw the mapping connections

1.On the mapping, draw the connections between the JSON component and the CSV component as shown below.

mf_json_04

For more information, see Working with Connections.

 

Step 4: Preview the mapping

You can now quickly preview the transformation output by clicking on the Output tab. The result is:

 

Alethia Alonso,altethia@example.com,35,

Klaus Mauer,klaus@example.com,57,

Natsuo Shinohara,natsuo@example.com,29,

 

As the output shows, for each person object in the JSON file, a new row is created, and object properties are comma-separated, which is the intended behavior.

 

However, we have not mapped yet the birthday field which exists in the JSON instance file, even if it does not exist in the schema.

 

Step 5: Map additional properties

Recall that the field birthday is an additional property not defined in the schema. To read data from such fields, MapForce provides additional items on the JSON component.

 

In this example, in order to read data from the birthday field, add the following to the mapping:

 

1.A constant with the value "birthday", see Add a Constant to the Mapping.
2.A filter component, see Filters and Conditions.
3.The logical function equal, see Add a Built-in Function to the Mapping.

 

Next, connect the components as illustrated below. Connecting components this way instructs MapForce to look for an additional property with the name birthday, and, if a string value is found, copy it to Field4 of the text component.

mf_json_05

In the mapping above, the name item under property (additional) allows you to access the additional property by its name. The subsequent items (string, number, boolean, and so on) correspond to JSON data types. They can be used to access the actual JSON value of that type. In our example, the name of the additional property is "birthday" and the type is "string", which is why these two items are connected to the filter.

 

If you preview the transformation output at this time, the result is:

 

Alethia Alonso,altethia@example.com,35,4 July

Klaus Mauer,klaus@example.com,57,31 August

Natsuo Shinohara,natsuo@example.com,29,

 

As the output shows, the fourth CSV field now includes the value of the only additional property of type "string", which is, in this case, birthday. Also, since the third person does not have a birthday, no value is available in the CSV at the corresponding position.


© 2019 Altova GmbH