Work with JSON data in integrator.io

JSON is a JavaScript Object Notation text format for storing and transporting data. With JSON you can transfer large amounts of data between applications without losing or having to transform the data.

Contents

• Syntax, data types, and terms
• JSON in integrator.io
• Third-party apps using JSON
• JSON transformation examples
  • CSV to JSON
  • XML to JSON
  • NetSuite subrecords to JSON
• References and formatting

Syntax, data types, and terms

JSON syntax determines how you will read and write your JSON data. All data is available in name/value pairs.

{"Name":"Value"}

All name/value pairs are separated by a colon (:), written in double quotes (" "), surrounded by curly braces ({ }), and separated with commas (,). Please note that integers do not need to be surrounded by double-quotes.

{"Name":"John"}

JSON objects are also surrounded by curly braces.

{
  "Object": {
    "Name": "Value", 
    "Name": "Value",
    "Name":"Value"
  }
}

Example:

{
 "employee":{"name":"John", "age":30, "city":"New York"}
}

Arrays, or lists, are surrounded by brackets [ ].

{
"Employees":["Sally", "John"]
}

Each value in a name/value pair must be one of six (6) data types:

• A string – Any assortment of characters

  {"Name":"John"}

• A number – Any integer or floating point

  {"Number":43}
  

• An object (JSON object) – A set of name and value pairs surrounded by curly braces

  {
  "employee":{"name":"John", "age":30, "city":"New York"}
  }
  

• An array – A list of items surrounded in square brackets

  {
  "employees":["John", "Anna", "Peter"]
  }
  

• A Boolean – A true or false value

  {
    "Employee":{"name":"John", "active":"false"}
  }
  

• Null – A blank value

  {
    "Employee":{"name":"John", "middlename":"null", "active":"false"}
  }
  

There are many resources available to learn JSON in depth.

JSON in integrator.io

integrator.io primarily uses JSON to transfer data. While you can create and run your flows intuitively through the integrator.io interface, you will benefit from some familiarity with JSON to map your data.

Data mapping allows you to define how integrator.io formats the source application’s data to fit the schema of the destination application. Simply put, data mapping is what gets your data from one application to another with minimal errors. When mapping your data, you might need to rely on handlebars expressions using handlebar syntax. These expressions help you to:

• Map export and import application fields
• Perform dynamic arithmetic calculations on exported values
• Dynamically encode and decode data during integration

A simple handlebars expression uses the following syntax:

{{ expression }}

Let’s look at example data for importing Microsoft Dynamics 365 Business Central customers.

{
"Person":"Buyer",
"BuyerEmail":"email.at.email.com",
"BuyerName":"JohnSmith",
"ShippingAddress":
{
"Name":"JohnSmith",
"Phone":12345678,
"AddressLine1": "123 Main St",
"City": "Big City",
"StateOrRegion": "State"
}
}

To reference this customer data within handlebars, you can do something as simple as {{Person}} {{BuyerEmail}. In context, you are requesting this information:

"Person":"Buyer"
"BuyerEmail":"email.at.email.com"

The final product would be: Buyer email@.email.com

You can use dot-notation to gain access to nested properties, objects, or arrays, like {{ShippingAddress.Name}} or {{ShippingAddress.City}} The final product would be JohnSmith or Big City.

Example mapping data

You can also create more complicated handlebars block references so integrator.io can implement functionality during the mapping progress. The example below is copied from an Amazon Seller Central – Microsoft Dynamics 365 Business Central mapping. The block reference in Amazon Seller Central will map to the displayName in Microsoft.

{{#ifBuyerName}}{{BuyerName}}{{else}}{{ShippingAddress.Name}}{{/if}}

This reads as follows:

• If the BuyerName field isn’t null and includes the BuyerName, then use the BuyerName as the displayName in Microsoft Dynamics 365 Business Central
  • {{#ifBuyerName}}{{BuyerName}}

• If not
  • {{else}}

• Use the Shipping Address name field
  • {{ShippingAddress.Name}}

• End the block reference
  • {{/if}}

Third-party apps using JSON

There are various third-party clients you can use to send, receive, and modify JSON data from integrator.io, most commonly, Postman. While this method lacks some of the benefits of the integrator.io application, you can access your account via your third-party client using https://api.integrator.io/v1 and the REST API resources in the Help Center. Don’t forget to provide your bearer token authentication!

Example Postman call requesting a connection

JSON transformation examples

While some applications transfer JSON data unchanged, integrator.io sometimes parses data from other formats (such as CSV, XML, EDI, etc.) into JSON.

CSV to JSON

Comma-separated value (CSV) files are often and easily transformed into JSON data to be populated in an application. Each row of the CSV file contains a data record. Each record consists of one or more fields, separated by commas.

These files look like this:

And integrator.io’s JSON output looks like this:

{
"Person":"Shopper",
"BuyerEmail":"email.at.email.com",
"BuyerName":"JohnSmith"
}

{
"Person":"Shopper",
"BuyerEmail":"email.at.email.com",
"BuyerName":"JaneSmith"
}

XML to JSON

Like JSON, eXtensible Markup Language (XML) is a way to send and receive data via an API. XML and JSON are similar. An XML file looks something like this:

<Shopper1>
<person>Shopper</person>
<BuyerEmail>email.at.email.com</BuyerEmail>
<BuyerName>JohnSmith</BuyerName>
</Shopper1>

<Shopper2>
<person>Shopper</person>
<BuyerEmail>email.at.email.com</BuyerEmail>
<BuyerName>JaneSmith</BuyerName>
</Shopper1>

And when converted to JSON in integrator.io it looks similar to this:

{
"Person":"Shopper",
"BuyerEmail":"email.at.email.com",
"BuyerName":"JohnSmith"
}

{
"Person":"Shopper",
"BuyerEmail":"email.at.email.com",
"BuyerName":"JaneSmith"
}

NetSuite subrecords to JSON

Subrecords hold information about a parent record and are meaningful only within that context. The subrecord must be created, edited, removed, or viewed from its standard NetSuite record. These subrecords must be configured and mapped in integrator.io. You can map subrecords imported into NetSuite natively through integrator.io.

NetSuite subrecords

When transformed, the JSON structure can look like this:

[
  {
    "customer_name": "Test Customer",
    "email": "celigo@test.com",
    "line_items": [
      {
        "item_name": "Inventory Serial Item1",
        "item_amount": 245,
        "item_qty": 2,
        "item_location": 1,
        "inv_detail": [
          {
            "serialnumber": "inv01",
            "qty": 1
          },
          {
            "serialnumber": "inv02",
            "qty": 1
          }
        ]
      },
      {
        "item_name": "Inventory Serial Item2",
        "item_amount": 542,
        "item_qty": 1,
        "item_location": 1,
        "inv_detail": [
          {
            "serialnumber": "inv03",
            "qty": 1
          }
        ]
      }
    ]
  }
]

Notice the line items, Inventory Serial Item1 and Inventory Serial Item2, with a quantity of 2 and 1 respectively. These are NetSuite serialized items, as is apparent from the associated serial numbers.

References and formatting

• Read more on JSON.
• Read more on XML.
• Read more on handlebars expressions and syntax.
• Read more on handlebars expressions and syntax in integrator.io.
• Clean up your JSON code to make it human-readable.
• Add a file extension to your browser to view JSON code.