Apigee Integration: Using Apps Script for JSON data transformation with JSMapr library

[madhans]

This article describes an advanced data transformation solution for Apigee Integration using the Apps Script  task and JSMapr library for transforming JSON data.

Applies to ...

Apigee Integration, integration platform that helps connect existing data and applications, and surface them as easily accessible APIs (For data transformation in ApigeeX (API management) refer to Shaping, accessing, and converting messages)

Apps Script Task, a cloud-based JavaScript platform that lets you integrate with and automate tasks across Google products. (Refer to Data Mapping task for simple data mapping needs)

JSMapr, open source Javascript based JSON transformation library (Other Apps Script libraries can also be used)

Transformation

Consider an input JSON that looks as follows:

{
    "fName": "Fred",
    "lName": "Smith",
    "addresses": [
        {
            "addr": "177 Pine St",
            "city": "San Jose",
            "state": "CA",
            "zip": 95120,
            "addressType": "HOME"
        },
        {
            "addr": "10 S Almaden Blvd",
            "city": "San Jose",
            "state": "CA",
            "zip": 95113,
            "addressType": "WORK"
        }
    ],
    "username": "fsmith",
    "child": "Ryan"
}

and needs to be converted to the following JSON format:

{
  "firstName": "Fred",
  "lastName": "Smith",
  "addresses": [
      {
          "streetAddress": "177 Pine St",
          "city": "San Jose",
          "state": "CA",
          "zip": "95120",
          "addressType": "HOME"
      },
      {
          "streetAddress": "10 S Almaden Blvd",
          "city": "San Jose",
          "state": "CA",
          "zip": "95113",
          "addressType": "WORK"
      }
  ],
  "username": "fsmith",
  "children": [ "Ryan" ]
}

A handful of changes are involved here.

1. Many attributes need to be renamed (example: fName -> firstName)
2. Data type of certain attributes need to be changed (example: zip from integer to String)
3. A scalar value needs to be converted into an Array (example: children)

Such changes are often needed in integration flows including but not limited to

1. Transforming an input to an integration flow before passing it to an external API/Connector
2. Transforming the output of an external API/Connector to feed as input to a subsequent API/Connector
3. Combining JSON data from various sources into a final JSON structure.

JSMapr

JSMapr is a JavaScript library for transforming JSON objects. It is a self contained library with no other dependencies and can be used in both JavaScript (Apps Script) and node.js environments.

JSMapr helps express transformation rules in a declarative style and reduces the amount of code that needs to be written to achieve similar results with JavaScript.

For example:

    JSMapr.MAKEARRAY("/children"),

instructs that "children" attribute be converted into an single element Array.

JSMapr provides a wealth of operations for transformation

ADD
DEL
COPY
MOVE
TOSTRING
STRINGIFY
MAKEARRAY
ADDTOARRAY
MOVETOARRAY
MAP1
MAPEACH
FUNC1
FUNCEACH
SORTARRAY
EXEC
IFEXISTS
IFEQUAL
IFTYPE
IF
IFFUNC
LOCSEPARATOR

Use in Apps Script

Apigee Integration offers an  Apps Script based Apps Script task that is useful when you want to run custom scripts or reuse existing scripts in your integration.

You can use JSMapr in Apps Script for JSON data transformation needs. Follow the steps below to setup a sample.

1. Ensure you have access to Apigee Integration UI
2. In Integration UI, select or create an Integration. A simple API trigger based integration is sufficient to try this sample. 
3. In Integration UI, add Apps Script task by following this guide
4. In Apps Script editor, add a file, and add contents of jsmapr.js (save locally and copy paste into editor) to the project
   
5. In Apps Script editor, update Run.gs with the following code

   function run() {
     // Read an integration variable
     var employee = AppsScriptTask.getTaskParameter('employee');
   
     var myMap = [
       // change field names
       JSMapr.MOVE("/lName", "/lastName"),
       JSMapr.MOVE("/fName", "/firstName"),
       JSMapr.MOVE("/child", "/children"),
       JSMapr.TOSTRING("/zip1"),
       // change children to an array
       JSMapr.MAKEARRAY("/children"),
       // force zips to be strings and change street address name
       JSMapr.MAPEACH("/addresses",
         [
           JSMapr.TOSTRING("/zip"),
           JSMapr.MOVE("/addr", "/streetAddress")
         ]
       )
     ];
   
     var mapr = new JSMapr();
     mapr.setMapCommands(myMap);
     var newEmployee = mapr.map(employee);
   
     AppsScriptTask.setIntegrationVariable('employee-new', newEmployee);
   }
   ​

6. Add the following to Test.gs 

   
   function testSample() {
   
     // Create a new request
     let req = AppsScriptTaskTest.createNewTestRequest('json-test');
   
     var employee = {
       "fName": "Fred",
       "lName": "Smith",
       "addresses": [
         {
           "addr": "177 Pine St",
           "city": "San Jose",
           "state": "CA",
           "zip": 95120,
           "addressType": "HOME"
         },
         {
           "addr": "10 S Almaden Blvd",
           "city": "San Jose",
           "state": "CA",
           "zip": 95113,
           "addressType": "WORK"
         }
       ],
       "username": "fsmith",
       "child": "Ryan"
     };
   
     // Add a task parameter that references a JSON integration variable
     AppsScriptTaskTest.setEventParameterAndCreateReference(req, "employee", "employee", employee);
     // out-dated
     // AppsScriptTaskTest.setIntegrationVariableAndCreateReference(req, "emp", "employee", {name:"snape", age:35});
   
     let res = AppsScriptTaskTest.runTest(req, executeScript);
   
     var expectedOutput = {
       "addresses": [{
         "city": "San Jose",
         "state": "CA",
         "zip": "95120",
         "addressType": "HOME",
         "streetAddress": "177 Pine St"
       }, {
         "city": "San Jose",
         "state": "CA",
         "zip": "95113",
         "addressType": "WORK",
         "streetAddress": "10 S Almaden Blvd"
       }],
       "username": "fsmith",
       "lastName": "Smith",
       "firstName": "Fred",
       "children": ["Ryan"]
     }
   
     // Check the response for the expected integration variable and its corresponding values
     console.log("test output" + JSON.stringify(AppsScriptTask.getTaskParameter("employee-new"), null, 2));
   
     AppsScriptTaskTest.containsParameterValue(res, "employee-new", expectedOutput);
     // out-dated
     // AppsScriptTaskTest.containsIntegrationVariableWithValue(res, "employee-new", expectedOutput);
   }​

7. In Test.gs, hit "Run" to test the code locally and ensure the transformation works fine.
8. Using the "Deploy" button, deploy the Apps Script as a web app
9. In Integration UI, create variable "employee" and set it as "Taken in as inputs to the integration (input variables)"
10. In Integration UI, create variable "employee-new" and set it as "Returned as outputs of the integration (output variables)"
11. Publish and test the integration, provide the following as input 

    {
        "fName": "Fred",
        "lName": "Smith",
        "addresses": [
            {
                "addr": "177 Pine St",
                "city": "San Jose",
                "state": "CA",
                "zip": 95120,
                "addressType": "HOME"
            },
            {
                "addr": "10 S Almaden Blvd",
                "city": "San Jose",
                "state": "CA",
                "zip": 95113,
                "addressType": "WORK"
            }
        ],
        "username": "fsmith",
        "child": "Ryan"
    }​

12. The integration will run and return the following as output

     {
       "firstName": "Fred",
       "lastName": "Smith",
       "addresses": [
           {
               "streetAddress": "177 Pine St",
               "city": "San Jose",
               "state": "CA",
               "zip": "95120",
               "addressType": "HOME"
           },
           {
               "streetAddress": "10 S Almaden Blvd",
               "city": "San Jose",
               "state": "CA",
               "zip": "95113",
               "addressType": "WORK"
           }
       ],
       "username": "fsmith",
       "children": [ "Ryan" ]
     }
    ​

Apigee Integration receives the input JSON, invokes Apps Script task to perform the transformation using JSMapr and returns the output JSON.

To Debug

• For issues within Apigee integration refer to Troubleshooting guide
• For issues in Apps Script refer to the Troubleshooting guide

From here

The above sample is a simple scenario involving a single JSON input variable being transformed into a  output JSON variable. This can be embedded in a complex integration flow or a complex Apps Script involving multiple steps.