Connectors / Core / Data Mapper

Set up data mappings (updated: 1507802654922)

Data Mapper

The Data Mapper allows you to set up data mappings

Overview

Sometimes when working with data in your workflows, it is necessary to set up 'mappings' for your data. Two general scenarios which would necessitate use of the Tray Data Mapper are:

  1. You wish to automatically pass data from one service to another by specifying a mapping 'table' so that e.g. the 'name' field in Service 1 is automatically mapped to the 'item' field in Service 2.

The Data Mapper has a Map Keys operation for this purpose.

  1. You are pulling data from a service and need the results to be formatted so as to update a particular field in a database with a value that is more helpful for the users of the database (e.g. 'landline call' and 'cellphone call' could both be mapped to 'phone call').

The Data Mapper has a Map one value operation for this purpose.

There is also a third Map multiple values between objects operation which is explained below.

Note that the examples given below all use a dummy dataset which is generated using an Object Helpers step, with the 'JSON parse' operation:

parse-json-customers

This replicates a scenario whereby you have received a customer list via a webhook trigger or another service connector

The array of customers pasted into the Source box is:

[
{
"id": 1,
"first_name": "Barbabas",
"last_name": "Gibbard",
"email": "bgibbard0@behance.net",
"country": "Canada",
"status": "active"
},
{
"id": 2,
"first_name": "Clary",
"last_name": "Rigbye",
"email": "crigbye1@netvibes.com",
"country": "United States",
"status": "passive"
},
{
"id": 3,
"first_name": "Kelcey",
"last_name": "Fillingham",
"email": "kfillingham2@comsenz.com",
"country": "United States",
"status": null
},
{
"id": 4,
"first_name": "Micaela",
"last_name": "Shadwick",
"email": "mshadwick3@businesswire.com",
"country": "Canada",
"status": "active"
},
{
"id": 5,
"first_name": "Orv",
"last_name": "Polini",
"email": "opolini4@icio.us",
"country": "France",
"status": null
}
]

Map keys example

Sometimes you will need to transform the keys of your data into a format that is accepted by the service you are wishing to upload to.

For example, Salesforce dictates that the keys for the first and last names of a contact should be FirstName and LastName.

So if we have a list of customers such as the one above, we can loop through these customers with the loop connector and use the data mapper Map keys operation like this:

map-keys-workflow

In this case we set up the mappings as:

first_name > FirstName
last_name > LastName

And each customer in the above list will be transformed into the following format:

{
"id": 2,
"email": "crigbye1@netvibes.com",
"country": "United States",
"FirstName": "Clary",
"LastName": "Rigbye",
"status": "passive"
}

Map one value example

Continuing with the above customer dataset, it may be that you are wanting to feed this into a database which records customers by region and not by country. In which case we would want to map values for certain countries into particular regions.

So if we have set up a loop to go through the customers, we can then use a data mapper step and the Map one value operation.

The one Value it takes in this case is $.steps.loop-2.value.country

many-to-one-region-mapping-workflow

The full list of data mappings for this is:

France, Germany, United Kingdom, Belgium, Netherlands > Europe
United States, Canada > US and Canada

The result can then be fed into your chosen database.

In this case we use Google Sheets and the 'create row' operation. Note that while the id, first_name, last_name and email fields take their data from the loop helper, region takes it from the Data Mapper using $.steps.data-mapper-2.result:

upload-customers-to-sheets

So you will see that this operation does not return a complete object, but only one value.

The end result is customers and their region recorded in your chosen database:

sheets-show-customers

Map multiple values between objects example

With the above customer dataset, you may wish to ensure that certain values are marked as confidential. In this case you can use the Map multiple values between objects operation.

You can first set the available Values to be mapped to:

map-multiple-values-workflow

Then in Mappings you can list the fields whose values should be replaced with the specified value:

map-multiple-values-props-panel

As a result a new object will be returned for each customer:

{
"id": "CONFIDENTIAL",
"first_name": "Kelcey",
"last_name": "Fillingham",
"email": "CONFIDENTIAL",
"country": "United States",
"status": null
}

And the end result in your database will look something like:

confidential-customers

Map values

The Map values operation allows you to map any number of values to any other value in an array or object. You can either map all occurrences of the values or map specific values for specific object keys. The resulting output has the same shape as the original data but with the specified values changed.

INFORMATION: The 'Map values' operation is a 'deep' map. You may map values that are deeplynested within arrays or objects. Arrays are treated like objects with keys of 0, 1, 2 etc...

INFORMATION: Arrays and objects are treated like structures that contain values. Therefore,the 'From' mapping cannot be of type array or object, but the 'To' mapping can be of any typeincluding array and object.

With the above customer dataset, you may wish to map all instances of the value 'United States' to 'USA' regardless of key, and all null values associated with the status key to a value of 'NO DATA'. In this case you can use the Map values operation.

You can set the available Values to be mapped to:

map-values-workflow-props-panel

As you can see, the input is referencing the customer array $.steps.object-helpers-1.result (as defined above) and the mappings are as follows:

United States > USA (for all object keys)
null > NO DATA (for 'status' keys only)

As a result a new object will be returned. Here is the workflow with the debug panel showing the input and output of the Map values operation:

map-values-workflow-debug-panel

The full output showing all completed mappings is as follows:

{
"output": [
{
"id": 1,
"first_name": "Barbabas",
"last_name": "Gibbard",
"email": "bgibbard0@behance.net",
"country": "Canada",
"status": "active"
},
{
"id": 2,
"first_name": "Clary",
"last_name": "Rigbye",
"email": "crigbye1@netvibes.com",
"country": "USA",
"status": "passive"
},
{
"id": 3,
"first_name": "Kelcey",
"last_name": "Fillingham",
"email": "kfillingham2@comsenz.com",
"country": "USA",
"status": "NO DATA"
},
{
"id": 4,
"first_name": "Micaela",
"last_name": "Shadwick",
"email": "mshadwick3@businesswire.com",
"country": "Canada",
"status": "active"
},
{
"id": 5,
"first_name": "Orv",
"last_name": "Polini",
"email": "opolini4@icio.us",
"country": "France",
"status": "NO DATA"
}
]
}

Map objects to list

Sometimes you may need to alter the format of existing JSON data to be used for a further process. For example, the Microsoft SQL database requires data in key and value pair to insert the data in one call into a selected Microsoft SQL database table.

This is just an example for your understanding. There are several instances where this could be required.

So to understand how this is done, consider that we are receiving JSON data from a Webhook trigger, in reality you may receive this data in any number of other ways such as from the Salesforce or SurveyMonkey connector, through the CSV Editor, and so on.

In this example, the received JSON data is in the following format:

[{
"data": {
"id": "6012fe453f38ecab86530e97",
"first_name": "Brooks",
"last_name": "Hendrix",
"phone": "+1 (990) 546-2598"
},
"meta": {
"event": "create",
"type": "prospect"
}
},
{
"data": {
"id": "6012fe454256655c4da94179",
"first_name": "Crane",
"last_name": "Lindsey",
"phone": "+1 (932) 505-2355"
},
"meta": {
"event": "create",
"type": "account"
}
}
]

The Data Mapper connector using the 'Map object to list' operation converts the above JSON into an array of field key and value objects.

map-objects-to-list

The expected output should look similar to this:

map-objects-to-list-op

Later this data could be stored in a third-party service. In this case, we are using Microsoft SQL Database as a generic placeholder for any service or database you may wish to use.

map-objects-to-list-insert-records-in-db

Map objects (Flattening nested JSON)

Depending on the data source, sometimes there might be a requirement to flatten received nested JSON data.

This is possible using the 'Map objects' operation of the Data Mapper connector.

PLEASE NOTE: The method explained below can only be used for a fixed list of potential nested fields, i.e. you cannot just say 'whatever data comes through, just flatten it all'. You must know all the possible fields and their nested structure, and list them in the properties panel, as explained below.

Consider the received nested JSON is in the following format:

{
"data": {
"id": "129273",
"attributes": {
"department": "IT",
"branch": "London"
}
},
"meta": {
"deliveredAt": "2021-01-29T07:52:28.094+00:00"
}
}

Using the 'Map objects' operation the 'Mapping' field transforms the nested structure i.e. attributes.[column_name], to a flat structure, i.e. just the [column_name]. The 'Mapping' field contains a list of all the possible attributes that you wish to get a flat structure for.

mysql-process-dynamic-data-flat-json-1

Make sure to uncheck the 'Included unmapped' field. If you do not uncheck this field, it will include the fields from the received JSON data that you haven't listed in the Mappings field, i.e., something like this:

mysql-process-dynamic-data-included-unmapped-1

If the 'Included unmapped' field is unchecked the expected flat JSON output should look like this:

mysql-process-dynamic-data-flat-json-op

All Operations

Latest version:

3.5

Map keys

Given an object, maps values to new keys. Returns a new object with the new keys.

Map list to object

Map an array of key value objects to a single object.

Map multiple values between objects

Map values between objects. Returns a new object with the new properties.

Map objects

Map object fields to new keys.

Map objects to list

Map an object or an array of objects to an array of key value objects.

Map one value

Given a value, map according to defined mappings. Returns the result of the mapping. If no mapping is defined for the value, returns the default value, otherwise returns null if no default is defined.

Map values

Given an object, or an array of objects, maps one or more values to new value(s). You may limit the mapping to specific object keys. Returns a new object (or array) retaining the original structure but with mapped values.