# Post-mapping transformations (End User-defined schema)

In this article, you will go through some common data transformation challenges that you might run into while building ETL (extracting from source service A, transforming it using end user-defined schema, loading into destination service B) integrations.

In this article, you will go through some common data transformation challenges that you might run into while building ETL (extracting from source service A, transforming it using end user-defined schema, loading into destination service B) integrations.
For each example that follows:

* We will be using dummy data which you would get from the source service.
* We have the end user-defined schema.
* We will be showing possible pathways (Core connectors, JSON transformer, Javascript) for each example. You are free to take a different route as well.

## 1 - Preparing input for a Service

In this example, we are going to map the input data/dummy data which could be coming from a source service using the end user-defined mapping in the configuration step (config wizard/custom form). The basic idea here is to write a code that can respond dynamically to any mapping provided by the end user.
We have the following dummy data:

### Dummy data

```json
[
    \{
      "Email": "aditya@acme.io",
      "FirstName": "Aditya",
      "Id": "0054H000007Cp3iQAC",
      "IsActive": true,
      "LastName": "Singh",
      "Name": "Aditya Singh",
      "Title": "Automation Architect",
      "User_Team__c": "Builder Experience"
    \},
    \{
      "Email": "mike@acme.io",
      "FirstName": "Mike",
      "Id": "0054H220007Cp3iQAC",
      "IsActive": false,
      "LastName": "Hewitt",
      "Name": "Mike Hewitt",
      "Title": "Documentation Manager",
      "User_Team__c": "Customer Experience"
    \}
]
```

The mapping and expected input schema for two end users is shown below:

### End user 1:

### Mapping:

```json
\{
    "Email": "Email",
    "FirstName": "First Name",
    "Id": "Id",
    "IsActive": "Active",
    "LastName": "Last Name",
    "Name": "Full Name",
    "Title": "JobTitle",
    "User_Team__c": "Team"
\}
```

### Required input schema:

```json
[
  \{
    "Email": "aditya@acme.io",
    "First Name": "Aditya",
    "Id": "0054H000007Cp3iQAC",
    "Active": true,
    "Last Name": "Singh",
    "Full Name": "Aditya Singh",
    "JobTitle": "Automation Architect",
    "Team": "Builder Experience"
  \},
  \{
    "Email": "mike@acme.io",
    "First Name": "Mike",
    "Id": "0054H220007Cp3iQAC",
    "Active": false,
    "Last Name": "Hewitt",
    "Full Name": "Mike Hewitt",
    "JobTitle": "Documentation Manager",
    "Team": "Customer Experience"
  \}
]
```

### End user 2:

### Mapping:

End user 2 has provided a nested mapping. (`'Id'` and `'IsActive'` fields are to be nested under the `'Salesforce'` object in the required input schema)

```json
\{
    "Email": "Email",
    "FirstName": "First Name",
    "Id": "Salesforce.Id",
    "IsActive": "Salesforce.Active",
    "LastName": "Last Name",
    "Name": "Full Name",
    "Title": "JobTitle",
    "User_Team__c": "Team"
\}
```

### Required input schema:

```json
[
  {
    "Email": "aditya@acme.io",
    "First Name": "Aditya",
    "Last Name": "Singh",
    "Full Name": "Aditya Singh",
    "JobTitle": "Automation Architect",
    "Team": "Builder Experience",
    "Salesforce": \{
      "Id": "0054H000007Cp3iQAC",
      "Active": true
    \}
  },
  {
    "Email": "mike@acme.io",
    "First Name": "Mike",
    "Last Name": "Hewitt",
    "Full Name": "Mike Hewitt",
    "JobTitle": "Documentation Manager",
    "Team": "Customer Experience",
    "Salesforce": \{
      "Id": "0054H220007Cp3iQAC",
      "Active": false
    \}
  }
]
```

You can import this project [here](#). It contains two ways you could solve this:
The same code works for both end users and you can test this by editing the `'mappings' `config value in workflows.

### 1 - using JSON transformer

```javascript
(
$valuesofObject := $each(mappings, function($v, $k) {$v}); /* gets all target object fields */
/* We need to filter fields into nested and non-nested fields in order to map them properly. Nested fields contain a dot(.) */
$otherFields := $filter($valuesofObject, function($v, $i, $a) {$v ~> /^[^.]*$/}); /* this regex ignores fields that have a dot(.) */
$nestedFields := $filter($valuesofObject, function($v, $i, $a) {$v ~> /\./}); /* this regex only accepts fields that have a dot(.) */
$mappings_without_nesting := mappings.$sift(function($v, $k) {$v ~> /^[^.]*$/}); /* forms mapping object without nested fields */
$fields_without_nesting := $keys($mappings_without_nesting); /* forms an array of all non-nested source keys ie. keys of mappings object */
/* map input data array */
[$map(data, function($item, $index) {  
    /* map non-nested target keys of mappings object */
    $merge([$map($otherFields, function($v, $i) {
            \{
                $v: $lookup($item, $fields_without_nesting[$i])
            \}
        }),
        /* map nested target keys of mappings objects */
        $merge($map($nestedFields, function($value, $k) {
        (
            $parent := $substringBefore($value, "."); /* extract parent field from nested target field */
            $regex_expression := $eval("/" & $parent & "/"); /* forms a regex to match mappings of this parent field */
            $nestedFieldsWithParent := $filter($nestedFields, function($v, $i, $a) {$v ~> $regex_expression}); /* filters fields of this parent from all nested fields */
            $mappings_with_nestedParent := mappings.$sift(function($v, $k) {$v ~> $regex_expression}); /* forms mappings object with fields of this parent */
            $fields_with_nestedParent := $keys($mappings_with_nestedParent);
            {
                /* map fields of this parent */
                $parent : $merge($map($nestedFieldsWithParent, function($value, $k) {
                    \{
                        $substringAfter($value, "."): $lookup($item, $fields_with_nestedParent[$k])
                    \}
                }))
            } 
        )
        }))
    ])
    })
])
```

### 2 - using Script

```javascript
// You can reference the input variables using input.NAME
// Parsed JSON files could be referenced as fileInput
exports.step = function(input, fileInput) {
    function getKeyByValue(object, value) \{
        return Object.keys(object).find(key => object[key] === value);
    \}
    let allFields = Object.values(input.mappings);
    let otherFields = allFields.filter((field) => field.match(/^[^.]*$/));
    let nestedFields = allFields.filter((field) => field.match(/\./));
    let output = [];
    input.data.map((item, index) => {
        let outputObj = {};
        for (let i = 0; i < otherFields.length; i++) \{
            let fieldName = otherFields[i];
            outputObj[fieldName] = item[getKeyByValue(input.mappings, fieldName)];
        \}
        for (let i = 0; i < nestedFields.length; i++) {
            let fieldName = nestedFields[i];
            let parent = fieldName.split(".")[0];
            let child = fieldName.split(".")[1];
            if (!outputObj.hasOwnProperty(parent)) \{
                outputObj[parent] = new Object();
                outputObj[parent][child] = item[getKeyByValue(input.mappings, fieldName)];
            \} else
                outputObj[parent][child] = item[getKeyByValue(input.mappings, fieldName)];
        }
        output.push(outputObj);
    })
    return output;
}
```

You can also play around with data and mappings in the playground. Here are the JSONata exerciser solution links for:

* [End user 1](https://try.jsonata.org/-pcMtMJd5)
* [End user 2](https://try.jsonata.org/51QWc-2md)
  Here is the playground [link](https://jsfiddle.net/coder_jedi/c23fbrtn/) if you want to play with the JavaScript solution.

### 2 - Preparing input for Mailchimp batch subscribe operation

We have the following dummy data:

### Dummy data

```json
[
   \{
      "FirstName":"Luke",
      "LastName":"Skywalker",
      "MailingStreet":"Tatooine",
      "MailingCity":"Rebel City",
      "MailingState":"Illinois",
      "MailingCountry":"USA",
      "MailingPostalCode":"60007",
      "Email":"luke@jedi.org"
   \},
   \{
      "FirstName":"Anakin",
      "LastName":"Skywalker",
      "MailingStreet":"Death star",
      "MailingCity":"Empire city",
      "MailingState":"Illinois",
      "MailingCountry":"USA",
      "MailingPostalCode":"60010",
      "Email":"anakin@theempire.org"
   \}
]
```

Mailchimp Connector's Raw HTTP operation to batch subscribe members will expect an input schema which depends on the user-defined mapping.
The mapping and expected input schema for two end users is shown below:

### End user 1:

### Mapping:

```json
\{
   "FirstName":"FNAME",
   "LastName":"LNAME",
   "MailingStreet":"ADDRESS.addr1",
   "MailingCity":"ADDRESS.city",
   "MailingState":"ADDRESS.state",
   "MailingCountry":"ADDRESS.country",
   "MailingPostalCode":"ADDRESS.zip"
\}
```

### Required input schema:

```json
[
   {
      "email_address":"luke@jedi.org",
      "status":"subscribed",
      "merge_fields":{
         "FNAME":"Luke",
         "LNAME":"Skywalker",
         "ADDRESS":\{
            "addr1":"Tatooine",
            "city":"Rebel City",
            "country":"USA",
            "zip":"60007",
            "state":"Illinois"
         \}
      }
   },
   {
      "email_address":"anakin@theempire.org",
      "status":"subscribed",
      "merge_fields":{
         "FNAME":"Anakin",
         "LNAME":"Skywalker",
         "ADDRESS":\{
            "addr1":"Death star",
            "city":"Empire city",
            "country":"USA",
            "zip":"60010",
            "state":"Illinois"
         \}
      }
   }
]
```

### End user 2:

### Mapping:

```json
\{
   "FirstName":"FNAME",
   "LastName":"LNAME",
   "MailingStreet":"ADDRESS.addr1",
   "MailingCity":"ADDRESS.city",
   "MailingState":"ADDRESS.state",
   "MailingCountry":"ADDRESS.country"
\}
```

### Required input schema:

```json
[
   {
      "email_address":"luke@jedi.org",
      "status":"subscribed",
      "merge_fields":\{
         "FNAME":"Luke",
         "LNAME":"Skywalker"
      \}
   },
   {
      "email_address":"anakin@theempire.org",
      "status":"subscribed",
      "merge_fields":\{
         "FNAME":"Anakin",
         "LNAME":"Skywalker"
      \}
   }
]
```

> **Info:** The '[batch subscribe members](https://mailchimp.com/developer/marketing/api/lists/batch-subscribe-or-unsubscribe/)' API call expects [merge\_fields](https://mailchimp.com/developer/marketing/docs/merge-fields/) to be sent. Here ADDRESS fields need special attention as ADDRESS fields are mapped as a nested object and will only be mapped if mapping for each one of 'addr1', 'city', 'state' and 'zip' has been provided by the end user.

> **Info:** Please refer '[Determining Input schema](https://tray.io/documentation/tray-uac/building-automations/transforming-data-for-input/satisfying-input-schema/)' page which shows how to find out input and output data format for services.

You can import this project [here](#). It contains two ways you could solve this:

### 1 - using JSON transformer

```json
(
$valuesofObject := $each({$.config.merge_fields_mapping}, function($v, $k) {$v});
$otherFields := $filter($valuesofObject, function($v, $i, $a) {$v ~> /^(?!.*ADDRESS).*/});
$addressFields := $filter($valuesofObject, function($v, $i, $a) {$v ~> /^ADDRESS/});
$merge_fields_mapping_without_address := {$.config.merge_fields_mapping}.$sift(function($v, $k) {$v ~> /^(?!.*ADDRESS).*/});
$merge_fields_mapping_with_address := {$.config.merge_fields_mapping}.$sift(function($v, $k) {$v ~> /^ADDRESS/});
$fields_without_address := $keys($merge_fields_mapping_without_address);
$fields_with_address := $keys($merge_fields_mapping_with_address);
$reqAddressFields := $filter($valuesofObject, function($v, $i, $a) {
    $v = "ADDRESS.addr1" or $v = "ADDRESS.city" or $v = "ADDRESS.state" or $v = "ADDRESS.zip"
});
$count($reqAddressFields) = 4 ? 
[$map($, function($item, $index) {
    {
        'email_address': $item.Email,
        'status': {$.config.mailchimp_subscription_status},
        'merge_fields': $merge([$map($otherFields, function($v, $i) {
            \{
                $v: $lookup($item, $fields_without_address[$i])
            \}
        }),{
            "ADDRESS": $merge($map($addressFields, function($value, $index) {
            \{
                $substringAfter($value, "ADDRESS."): $lookup($item, $fields_with_address[$index])
            \}
        }))
        }])
    }
})] 
: [$map($, function($item, $index) {
    {
        'email_address': $item.Email,
        'status': {$.config.mailchimp_subscription_status},
        'merge_fields': $merge([$map($otherFields, function($v, $i) {
            \{
                $v: $lookup($item, $fields_without_address[$i])
            \}
        })
        ])
    }
})];
)
```

### 2 - using Script

```json
// You can reference the input variables using input.NAME
// Parsed JSON files could be referenced as fileInput
exports.step = function(input, fileInput) {
    function getKeyByValue(object, value) \{
        return Object.keys(object).find(key => object[key] === value);
    \}
    let allFieldsToBeMapped = Object.values(input.merge_fields_mapping);
    let otherFields = allFieldsToBeMapped.filter((field) => field.match(/^(?!.*ADDRESS).*/));
    let addressFields = allFieldsToBeMapped.filter((field) => field.match(/^ADDRESS/));
    let reqAddressFields = addressFields.filter((field) => field === "ADDRESS.addr1" || field === "ADDRESS.city" || field === "ADDRESS.state" || field === "ADDRESS.zip")
    let output = [];
    input.data.map((item, index) => {
        let outputObj = {};
        outputObj.email_address = item.Email;
        outputObj.status = input.mailchimp_subscription_status;
        outputObj.merge_fields = new Object();
        for (let i = 0; i < otherFields.length; i++) \{
            let fieldname = otherFields[i];
            outputObj.merge_fields[fieldname] = item[getKeyByValue(input.merge_fields_mapping, fieldname)];
        \}
        if (reqAddressFields.length === 4) {
            outputObj.merge_fields.ADDRESS = new Object();
            for (let i = 0; i < addressFields.length; i++) \{
                let fieldname = addressFields[i].split('.')[1];
                outputObj.merge_fields.ADDRESS[fieldname] = item[getKeyByValue(input.merge_fields_mapping, addressFields[i])];
            \}
        }
        output.push(outputObj);
    })
  return output;
};
```