Simplify your Data Model using JSON data fields

Grouping related data in a JSON data field can significantly shorten your data model and make it easier to navigate. This is especially useful for datasets that contain information for shipping addresses, billing information, recipient details, policy information or invoice metadata.

JSON data fields present the data in a tree- or folder-like structure that can be collapsed or expanded. Simply drag and drop the nested fields (also known as properties) into your template, to insert placeholders or expressions.

In this tutorial, you will learn how to create JSON data fields in the DataMapper perspective of OL Connect Designer. We’ll use an Action Step, but the same technique can be used when using JavaScript in an Extraction Step.

In our example, we’re working with XML data. However, it’s important to note that JSON data fields are a versatile technique for grouping fields, and they can be used with all input data formats.

Sample data

Take a look at the data below, filled with various fields that provide customer/recipient information. Our goal is to group this data into a single JSON data field. Let’s get started!

Steps

Creating a JSON data field

To create a JSON data field:

  1. Right click the record in the Data Model pane and choose Add Field… from the contextual menu.
  2. Enter a name for the field, set its Type to JSON and enter an empty object as its Default value. The latter is done by entering two curly braces (Update! This is no longer needed in OL Connect 2023.2 or later). See the following image:
  1. Click OK to add the field to the Data Model.

Populating the JSON data field

To populate a JSON data field using an Action step:

  1. Click the Add Action Step icon on the toolbar. This adds an Action step to the Steps pane.
  2. Select the Action step and locate the Run JavaScript field in the Step Properties pane.
  3. Delete the contents of the Expression field and add an empty JavaScript object as show below.
let contact = {}

There are several ways to add properties to the object. In the following steps we will be using the Dot notation and data.extract() to populate properties like first, last, phone etc.

  1. On the line below our contact variable, type: 
contact.first =
  1. Select the <ContactFirstName> field in the XML viewer and click the Use selection icon to the lower right of the Expression editor. This inserts the method to extract the respective value. The scripts looks like this:
let contact = {}
contact.first = data/extract('./invoice[1]/ContactFirstName[1]')
  1. Repeat step 4 and 5 for ContactLastName, ContactPhone, ContactEmail and Company data.
    At this stage all information is captured in our contact variable.
    The next step is to write this to a data field.
  2. To write a value to a data field we need to target the respective field in the record object and in case of a JSON data field write our JavaScript object to a JSON string using JSON.stringify(). The complete script looks like this:
let contact = {}
contact.first   = data.extract('./invoice[1]/ContactFirstName[1]')
contact.last    = data.extract('./invoice[1]/ContactLastName[1]')
contact.phone   = data.extract('./invoice[1]/ContactPhone[1]')
contact.email   = data.extract('./invoice[1]/ContactEmail[1]')
contact.company = data.extract('./invoice[1]/Company[1]')

record.fields.contact = JSON.stringify( contact )

The following image shows the result in the Data Model view. Use the arrow icon next to the name of the field to expand or collapse the data field.

When creating and populating a JSON field as part of an Extraction step, the last line of the script needs to be slightly different. In that scenario the stringified object must be returned directly, as shown below:

Using JSON data field properties in a template

The image below shows the properties of our JSON data field in a template. Notice how the expressions use the Dot notation to target the properties. Note that using expressions requires you to enable the validation of Handlebars expressions in the Properties of the section. For documents created in OL Connect 2022.2 and higher this is the default behavior.