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
- Data types
- Set the data type of a JSON element
- JSON record and row structures
- JSON in integrator.io
- Third-party apps using JSON
- JSON transformation examples
- References and formatting
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 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 in the set must be separated by 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 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 (
) button to the right of any field mapping to change its data type.
Select an option from the Data type list.
Using Mapper 2.0, the data type displays to the right of the name of each JSON element.
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 either 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 with 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 by 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 square 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 group your export data, the data passes through the rest of the flow in row structure.
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.0The 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 connectionJSON 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 subrecordsWhen 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.
Comments
0 comments
Please sign in to leave a comment.