JSON Data Mapping and Transformation with MapForce

JSON is a popular format for transferring data between systems thanks to its simple markup, small footprint, and heritage based on the JavaScript programming language. MapForce supports JSON as both an input and output format for JSON data mapping and transformation. For instance, MapForce can extract information from any popular database and produce a JSON file ready for transfer.
The Requirement: Here is an example of a typical need for JSON data mapping: A manufacturing company controls costs by exploiting a just-in-time assembly process with very little parts inventory on hand. New customer orders are logged in a sales database, and at the end of every day the components needed to assemble that day’s sales are tabulated via a query into the database. The required parts will be ordered from suppliers via a purchase order transferred in JSON format.

We need two components to design the MapForce JSON data mapping and transformation to generate the purchase order: a SQL query into the database and a JSON instance document to model the purchase order. We’ve written before about database mapping, most recently in Database Features Across the Altova Product Line, so we will focus here on the JSON component.

Here is a view of the JSON instance file that is a model for the data mapping output:

If you need to create a JSON instance from scratch, you can type it directly into XMLSpy, taking advantage of syntax coloring, context-sensitive entry helpers, structural marking to match brackets and braces, and other JSON editing features. If you’re not familiar enough with JSON to type it in directly, you can also use XMLSpy to convert an XML instance to a JSON file.

When we drop the JSON instance file into a data mapping, MapForce will automatically generate a JSON schema file from the instance, based on the JSON Draft 04 Schema (http://tools.ietf.org/html/draft-zyp-json-schema-04).

The JSON schema is added to the mapping and its data elements may be mapped as either inputs or outputs:

The MapForce database query window offers a quick place to test or refine the SQL query:

We can insert the SQL query into the mapping to provide the source data for the JSON file:

Then we connect the SQL query results to the data elements of the JSON component:

Note that the select statement itself is mapped to the object declaration beneath the row array (highlighted in red above). This will force each row of data in the query result to create a new JSON object in the output file.

The last remaining item to complete the JSON data mapping and transformation is the date. We need to provide the order date in the output file, and we also need to refine the SQL query to always select data for the current day. MapForce includes a datetime function called now that can serve both needs.

Here is the completed JSON data mapping:

We applied additional datetime functions to extract the month and day to provide parameters to the SQL query, and we stripped the time zone from the now function to provide the date for the JSON output. (We didn’t provide a parameter for the year because our testbed sales database only has orders for one year.)

Clicking the Output button under the MapForce mapping window causes the mapping to be executed and the output is displayed:

Now the JSON data mapping and transformation is ready to run at the end of every day to generate the JSON purchase order for parts needed for the manufacturing line. We can automate the process by saving the mapping as a MapForce Server execution file for processing by MapForce Server, where it can be processed via a batch file, under control of FlowForce Server, or via the MapForce Server API.

Or, to dig deeper into the mapping, we can open the JSON schema that MapForce automatically generated from the instance file in XMLSpy and examine it in XMLSpy Schema view:

MapForce is a powerful data mapping tool for mapping and transforming JSON data to or from any of: XML, JSON, databases, EDI, XBRL, flat files, Excel and/or Web services. To try it out for your own JSON data application download a fully functional free trial today!