integrator.io moves data between applications as JSON, and mapping decides the shape of that JSON. This article is for builders who need to know which JSON structures integrator.io reads and writes, what each data type means, and how CSV and XLSX files are generated from mapped data.
JSON basics
JSON stores data as name-value pairs. Names and string values are wrapped in double quotes. A colon separates the name from the value. Numbers and Booleans don't use quotes.
{ "name": "John", "age": 30, "active": true }An object is a set of name-value pairs in curly braces { }. Items are separated by commas, and an object can contain other objects and arrays.
{
"employee": {
"name": "John",
"age": 30,
"city": "New York"
}
}An array is a list in brackets [ ]. It can hold values, objects, or other arrays.
{
"names": ["Sally", "John", "Ben"],
"orders": [
{ "id": "A1", "quantity": 2 },
{ "id": "A2", "quantity": 1 }
]
}Data types
The data type of a destination field sets the structure and formatting of the JSON that integrator.io sends. Check the destination API's documentation for the types it expects. To set the type, select Settings in Mapper 1.0, or select the type beside the field name in Mapper 2.0. For the validation rules for each type, see Data type validation reference.
| Data type | Description | Example |
|---|---|---|
| string | Any characters in double quotes. | { "name": "John" } |
| number | An integer or decimal, without quotes. | { "number": 43 } |
| Boolean |
true or false, without quotes. |
{ "active": false } |
| object | Name-value pairs in curly braces. | { "employee": { "name": "John", "age": 30 } } |
| string array | A list of strings. | { "employees": ["John", "Anna", "Peter"] } |
| number array | A list of numbers. | { "scores": [98, 99, 100] } |
| Boolean array | A list of Booleans, without quotes. | { "isEmployed": [false, true, false] } |
| object array | A list of objects. | { "order": [{ "line_item": "03e7eb26f1" }, { "line_item": "p3e66626l1" }] } |
Mapper 2.0 also offers Date. For the date format, see Format date and time in ISO8601.
JSON record and row structures
A valid structure is a record or a row.
- A record opens and closes with curly braces
{ }. A JSON object is a record. - A row opens and closes with brackets
[ ]. A row is an array of records.
{ "order": { "customerid": "03e7eb26f1", "quantity": 1 } }[
{ "order": { "customerid": "03e7eb26f1", "quantity": 1 } },
{ "order": { "customerid": "p3e66626l1", "quantity": 4 } }
]When you group export data, the data passes through the rest of the flow as rows. To set the structure of mapped output, see Map data with Mapper 2.0.
Reference fields with handlebars
Handlebars expressions use the syntax {{expression}} to insert source values into a mapping. Use dot notation to reach nested fields. For example, with this source record:
{
"Person": "Buyer",
"BuyerEmail": "email@email.com",
"BuyerName": "John Smith",
"ShippingAddress": { "Name": "John Smith", "City": "Big City" }
}{{Person}} {{BuyerEmail}} returns Buyer email@email.com, and {{ShippingAddress.City}} returns Big City.
A block expression maps a value conditionally. This expression uses BuyerName when it has a value, and ShippingAddress.Name otherwise:
{{#if BuyerName}}{{BuyerName}}{{else}}{{ShippingAddress.Name}}{{/if}}
Handlebars expressions also let you perform dynamic arithmetic calculations on exported values and dynamically encode and decode data during an integration.
For the full syntax, see Handlebars syntax and the Handlebars expression reference.
Access JSON from third-party applications
Third-party clients, such as Postman, can send, receive, and modify JSON data from integrator.io. A third-party client may lack some of the benefits of the integrator.io interface. To access your account from a client, use this endpoint:
https://api.integrator.io/v1
You can also use the integrator.io REST API resources. Use bearer token authentication to access these resources from third-party applications.
Formats that integrator.io converts to JSON
When an export reads another format, integrator.io converts it to JSON before mapping.
CSV to JSON
Each row of a comma-separated value (CSV) file becomes one record. The header row supplies the field names. For example, this CSV file:
Person, BuyerEmail, BuyerName Shopper,email.at.email.com,JohnSmith Shopper,email.at.email.com,JaneSmith
becomes these two records:
{ "Person": "Shopper", "BuyerEmail": "email.at.email.com", "BuyerName": "JohnSmith" }
{ "Person": "Shopper", "BuyerEmail": "email.at.email.com", "BuyerName": "JaneSmith" }XML to JSON
The XML element names become field names in the JSON record. For example, this XML:
<Shopper1> <person>Shopper</person> <BuyerEmail>email.at.email.com</BuyerEmail> <BuyerName>JohnSmith</BuyerName> </Shopper1>
becomes a record that looks like this:
{ "Person": "Shopper", "BuyerEmail": "email.at.email.com", "BuyerName": "JohnSmith" }NetSuite subrecords to JSON
- A subrecord becomes a nested array inside its parent record. For example, serial number details for a line item appear as an
inv_detailarray inside that line item.
CSV and XLSX output
When you map JSON data into a CSV or XLSX file, integrator.io can flatten the data into columns in one of two ways. The output depends on the parsing behavior of your account.
Mapper 2.0 output:
{
"id": "1",
"customer": { "name": "John", "age": "30", "city": "New York" },
"addresses": [{ "city": "Seattle", "state": "WA" }]
}| Parsing behavior | CSV output | Result |
|---|---|---|
| Previous |
id,name,age,city,state1,John,30,New York,WA
|
Splits nested values into columns and drops the parent keys customer and addresses. |
| New |
id,customer,addresses1,"{""name"":""John"",""age"":30,""city"":""New York""}","[{""city"": ""Seattle"",""state"": ""WA""}]"
|
Keeps each nested object or array as JSON in one column, so arrays are processed accurately. |
"age": "30" in the input and 30 without quotes in the new output.
Connections created after the January 2025 release use the new behavior. Connections created earlier keep the previous behavior until you contact Celigo Support to switch your account.
More resources
- JSON introduction
- XML introduction
- Handlebars expressions and syntax
- Clean up your JSON code to make it human-readable