Skip to main content

Work with JSON data in integrator.io

Tom Santiago
  • Updated

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

JSON fundamentals

JSON syntax is organized in name/value pairs.

{"Name": "Value"}

The name and its value are each:

  • wrapped in double quotes (“”)
  • separated by a colon (:)
  • surrounded by curly braces ({ }).

Numbers are not surrounded by double-quotes.

{"Number": 100}

A JSON object is a JSON structure that includes multiple name/value pairs.

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

JSON objects are also surrounded by curly braces {}, and each item except for the last one in the set must end with a comma. This is a JSON object named “employee” that contains three fields: “name”, “age”, and “city”.

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

A name/value pair can contain multiple values within an array. Arrays are lists, are surrounded by brackets [ ], and the values included in an array are separated by commas.

{
     "Names":["Sally", "John", “Ben”]
}

Arrays can contain fields.

[
     "Name": "Sally", “Name”: “Joe”, Name: “Eunice”
]

Arrays can contain objects.

[
{
  "Object1": {
    "Name": "Value", 
    "Number": 100,
    "Name":"Value"
  }
},
{
  "Object2": {
    "Name": "Value", 
    "Number": 100,
    "Name":"Value"
  }
},
{
  "Object3": {
    "Name": "Value",
    "Number": 100,
    "Name":"Value"
  }
}
]

Arrays can contain arrays.

{
     “ContainerArray”: [
          “NestedArray1”: [
                 “field1”: “value”,
                 “field2”: “value2”
          ],
          “NestedArray2”: [
                 “field1”: “value”,
                 “field2”: “value2”
          ]
     ]
}

Objects can also contain objects and arrays.

{
     “ContainerObject1”: {
          “NestedArray1”: [
                 “field1”: “value”,
                 “field2”: “value2”
          ],
          “NestedArray2”: [
                 “field1”: “value”,
                 “field2”: “value2”
          ],
          { 
          "Object1": { 
               "Name": "Value", 
               "Number": 100, 
               "Name":"Value" 
          }
     }
}

To learn more about JSON, visit w3schools.com.

Data types

When creating destination fields from source fields during mapping, the data type assigned to each destination field determines characteristics of the JSON structure. The destination API expects some fields to be numbers, some to be strings, and usually all of them must be inconveniently nested within complex objects and arrays. It is important for you to have a model of the JSON structure you want to build readily available to reference. You can usually find some version of this in the destination API’s developer documentation.

Set the data type of a JSON element

Click the Settings (Settings (gear) button) button to the right of any field mapping to change its data type.

mapper1Settings.png

Use the Data type drop-down list to set the data type.

Mapper_1.0.png

Using Mapper 2.0, the data type displays to the right of the name of each JSON element.

SetDataType.png

Click the data type to select from the following:

string

A string value is any combination of characters surrounded by double quotes.

{
     "Name": "John"
}

number

A number value is any integer or floating point. Number values do not use quotes.

{
     "Number": 43
}

Boolean

A Boolean value can only be true or false. Boolean values do not use quotes.

{
     "active": false
}

object

An object is a set of name and value pairs surrounded by curly braces. The following JSON object contains three fields: “name”, “age”, and “city”.

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

[string]

A string array is a list of string values separated by commas and surrounded in square brackets.

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

[number]

A number array is a list of number values separated by commas and surrounded in square brackets.

{
     "employees": [98, 99, 100]
}

[Boolean]

A Boolean array is a list of Boolean values separated by commas and surrounded in square brackets. The values in a Boolean array do not use quotes.

{
     "isEmployed": [false, true, false]
}

[object]

An array object uses an array structure that contains a series of comma separated items.

{
     "order": [
          {
               "line_items": "03e7eb26f1"
          },
          {
               "line_items": "p3e66626l1"
          }
     ]
}

JSON record or row structures

Valid JSON structures come in one of two forms: record or row. 

JSON record structures

Record structures open and close with curly braces {}. JSON objects are record structures.

This is an example of a record structure:

{
     "order": {
     "line_items": [
          {
               "customerid": "03e7eb26f1",
               "quantity": 0
          },
          {
               "listOfThings": {
                    "first_name": "Joe",
                    "last_name": "Smith"
          }
     ]
}

The defining characteristics of a record structure are the opening and closing curly braces {}.

JSON row structures

Row structures open and close with brackets []. Rows are arrays of JSON objects (or record structures).

This is an example of a row structure:

[
     "order": { 
     "line_items": [ 
          { 
               "customerid": "03e7eb26f1", 
               "quantity": 0 
          },
          {
               "listOfThings": { 
                    "first_name": "Joe",
                    "last_name": "Smith" 
          }
     ]
]

The defining characteristics of a row structure are the opening and closing brackets []. When you use grouping for your export data, the data passes through the rest of the flow in row structure.

JSONPath notation

A JSONPath expression specifies a path to an element (or a set of elements) in a JSON structure. Only Mapper 2.0 supports JSONPath notation. Future releases will extend JSONPath notation support to other parts of integrator.io. Learning this skill is essential to mapping fields with Mapper 2.0.

JSONPath reference operators

Expression Description
$

The root object or array element. The leading $ represents the root object or array and can be omitted. For example, $.foo.bar and foo.bar are the same, and so are $[0].status and [0].status.

. or []

child operator:

You can reference paths with dot notation:

$.store.book[0].title

or bracket notation:

$['store']['book'][0]['title']

or a mix of dot and bracket notations:

$['store'].book[0].title

Note: Dots are only used before property names not in brackets.

..

recursive descent. JSONPath borrows this syntax from E4X.

[]

subscript operator. XPath uses it to iterate over element collections and for predicates. In Javascript and JSON it is the native array operator.

[,]

Union operator in XPath results in a combination of node sets. JSONPath allows alternate names or array indices as a set.
 

script expression, using the underlying script engine.

?()

applies a filter (script) expression.

.property

The specified property in a parent object.

['property']

The specified property in a parent object. Use single quotes around the property name.

Tip: Use this notation if the property name contains special characters such as spaces, or begins with a character other than A..Za..z_.

[n]

The n-th element from an array. Indexes are 0-based.

[index1,index2,…]

The array elements with the specified indexes.

..property

Recursive descent: Searches for the specified property name recursively and returns an array of all values with this property name. Always returns a list, even if just one property is found.

*

Wildcard selects all elements in an object or an array, regardless of their names or indexes. For example, address.* means all properties of the address object, and book[*] means all items of the book array. wildcard. All objects/elements regardless their names.

[start:end]

[start:]

Selects array elements from the start index and up to, but not including, end index. If end is omitted, selects all elements from start until the end of the array. Returns a list.

[start:end:step]

array slice operator borrowed from ES4.

[:n]

Selects the firstn elements of the array. Returns a list.

[-n:]

Selects the last n elements of the array. Returns a list.

[?(expression)]

Filter expression. Selects all elements in an object or array that match the specified filter. Returns a list.

[(expression)]

Script expressions can be used instead of explicit property names or indexes. An example is [(@.length-1)] which selects the last item in an array. Here, length refers to the length of the current array rather than a JSON field named length.

@

Used in filter expressions to refer to the current node being processed. the current object/element

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

Referencing fields with handlebars expressions

A simple handlebars expression uses the following syntax:

{{expression}}

Let’s look at example data for importing Microsoft Dynamics 365 Business Central customers. Here is an example customer record:

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

To reference this customer data with handlebars, you can use:

{{Person}} {{BuyerEmail}}

In context, you are requesting this information:

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

The final product would be:

Buyer email@email.com

You can use dot-notation to gain access to nested properties, objects, or arrays.

{{ShippingAddress.Name}} or {{ShippingAddress.City}}

In context, you are requesting this information:

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

The final product would be:

JohnSmith or Big City

You can use handlebars block references when mapping fields. The following example shows the complete mapping configuration for Amazon Seller Central (source app) – Microsoft Dynamics 365 Business Central (destination app).

Example mapping data with Mapper 1.0

The handlebars block reference expression demonstrated below conditionally maps BuyerName to displayName.

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

The expression works as follows:

If the BuyerName field is not null (contains a value)...

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

Then use the BuyerName value for the displayName value in the destination app.

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

Otherwise...

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

Use the ShippingAddress.Name field.

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

Third-party apps using JSON

Various third-party clients can send, receive, and modify JSON data from integrator.io. Postman is the most common. Third-party clients may lack some of the benefits of the integrator.io provides, but you can access your account via a third-party client using the following endpoint:

https://api.integrator.io/v1

You can also use integrator.io REST API resources. Use bearer token authentication to access these resources from third-party apps.

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:

Person, BuyerEmail, BuyerName
Shopper,email.at.email.com,JohnSmith
Shopper,email.at.email.com,JaneSmith

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

Related articles

Comments

0 comments

Please sign in to leave a comment.