Data Mapper

3.5

The Data Mapper allows you to set up data mappings

Overview

Copy

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:

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:

1
[
2
 {
3
  "id": 1,
4
  "first_name": "Barbabas",
5
  "last_name": "Gibbard",
6
  "email": "bgibbard0@behance.net",
7
  "country": "Canada",
8
  "status": "active"
9
 },
10
 {
11
  "id": 2,
12
  "first_name": "Clary",
13
  "last_name": "Rigbye",
14
  "email": "crigbye1@netvibes.com",
15
  "country": "United States",
16
  "status": "passive"
17
 },
18
 {
19
  "id": 3,
20
  "first_name": "Kelcey",
21
  "last_name": "Fillingham",
22
  "email": "kfillingham2@comsenz.com",
23
  "country": "United States",
24
  "status": null
25
 },
26
 {
27
  "id": 4,
28
  "first_name": "Micaela",
29
  "last_name": "Shadwick",
30
  "email": "mshadwick3@businesswire.com",
31
  "country": "Canada",
32
  "status": "active"
33
 },
34
 {
35
  "id": 5,
36
  "first_name": "Orv",
37
  "last_name": "Polini",
38
  "email": "opolini4@icio.us",
39
  "country": "France",
40
  "status": null
41
 }
42
]

Map keys example

Copy

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:

In this case we set up the mappings as:

1
first_name > FirstName
2
last_name > LastName

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

1
{
2
 "id": 2,
3
 "email": "crigbye1@netvibes.com",
4
 "country": "United States",
5
 "FirstName": "Clary",
6
 "LastName": "Rigbye",
7
 "status": "passive"
8
}

Map one value example

Copy

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

The full list of data mappings for this is:

1
France, Germany, United Kingdom, Belgium, Netherlands > Europe
2
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:

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:

Map multiple values between objects example

Copy

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:

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

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

1
{
2
 "id": "CONFIDENTIAL",
3
 "first_name": "Kelcey",
4
 "last_name": "Fillingham",
5
 "email": "CONFIDENTIAL",
6
 "country": "United States",
7
 "status": null
8
}

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

Map values

Copy

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.

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:

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:

1
United States > USA (for all object keys)
2
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:

The full output showing all completed mappings is as follows:

1
{
2
 "output": [
3
  {
4
   "id": 1,
5
   "first_name": "Barbabas",
6
   "last_name": "Gibbard",
7
   "email": "bgibbard0@behance.net",
8
   "country": "Canada",
9
   "status": "active"
10
  },
11
  {
12
   "id": 2,
13
   "first_name": "Clary",
14
   "last_name": "Rigbye",
15
   "email": "crigbye1@netvibes.com",
16
   "country": "USA",
17
   "status": "passive"
18
  },
19
  {
20
   "id": 3,
21
   "first_name": "Kelcey",
22
   "last_name": "Fillingham",
23
   "email": "kfillingham2@comsenz.com",
24
   "country": "USA",
25
   "status": "NO DATA"
26
  },
27
  {
28
   "id": 4,
29
   "first_name": "Micaela",
30
   "last_name": "Shadwick",
31
   "email": "mshadwick3@businesswire.com",
32
   "country": "Canada",
33
   "status": "active"
34
  },
35
  {
36
   "id": 5,
37
   "first_name": "Orv",
38
   "last_name": "Polini",
39
   "email": "opolini4@icio.us",
40
   "country": "France",
41
   "status": "NO DATA"
42
  }
43
 ]
44
}

Map objects to list

Copy

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:

1
[{
2
		"data": {
3
			"id": "6012fe453f38ecab86530e97",
4
			"first_name": "Brooks",
5
			"last_name": "Hendrix",
6
			"phone": "+1 (990) 546-2598"
7
		},
8
		"meta": {
9
			"event": "create",
10
			"type": "prospect"
11
		}
12
	},
13
	{
14
		"data": {
15
			"id": "6012fe454256655c4da94179",
16
			"first_name": "Crane",
17
			"last_name": "Lindsey",
18
			"phone": "+1 (932) 505-2355"
19
		},
20
		"meta": {
21
			"event": "create",
22
			"type": "account"
23
		}
24
	}
25
]

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

The expected output should look similar to this:

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 (Flattening nested JSON)

Copy

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.

Consider the received nested JSON is in the following format:

1
{
2
	"data": {
3
		"id": "129273",
4
		"attributes": {
5
			"department": "IT",
6
			"branch": "London"
7
		}
8
	},
9
	"meta": {
10
		"deliveredAt": "2021-01-29T07:52:28.094+00:00"
11
	}
12
}

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.

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:

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