Transformation Service Plugin

The plugin “Transformation Service Plugin” provides a transfomer API that allows to process JSON or XML data by applying transformations and rules and returning a key/value object with the result.

We will see in this post how it work and what are the limitations and go through a few examples.

Before going further, make sure that the plugin “Transformation Service Plugin” (com.glide.transform) is activated in your instance. If not activate it.

Overview of the API

There are in fact 3 APIs designed to work together to achieve the transformation of the data.

The 3 APIs are:

Transformer API, that provide the necessary functions to perform the transformation and retrieve the results;
TransformerDefinition API, that allows to define the transformation, using a set of rules and a path;
TransformerRuleList API, used to defined the transform rules to apply.

In a nutshell, you need to create all the transformation rules with TransformerRuleList, add the rules list to the TransformerDefinition and use the this definition with Transformer to actually process and transform the data.

Test case

For this post, we will use data coming from the user table, via a REST api call.

For the sack of simplicity, we will connect to the same instance. The goal here is just to get the data in a JSON format and play with the transform functionnality.

If necessary, for the REST message creation, you can have a look to the post about Using Remote Tables in Service-Now, as we are reusing the message here.

From now, we will aways use the following code to get the data with the subsequent examples:

var r = new sn_ws.RESTMessageV2('x_12270_remote_tab.my users', 'Get');
var response = r.execute();
var responseBody = response.getBody();

As a result, we get a JSON like this one, containig all the fields from the user table in the variable responseBody:

{
  "result": [
    {
      "calendar_integration": "1",
      "country": "",
      "user_password": "jF9fbZrY7c",
      "last_login_time": "2019-04-05 22:16:30",
      "source": "",
      ....
    },
	.....
  ]
}

Transformation rules

The first step is to define the transformation rules.

In our scenario we will start with rules to :

Concatenate the first_name and last_name and add a String “User " in front;
Replace the “email” user name by stars (”***"), but keep the domain part (“user@domain.com” becomes “***@domain.com”);
Define a “type”, that is either ACTIVE, INACTIVE, based on active field;
Use a few more fields as it

This gives us the following code, wrapped in a function getTransformerRuleList:

function getTransformerRuleList () {
var transformerRuleList = new sn_tfrm.TransformerRuleList()
  .fromJSON() /* indicate it is from JSON*/
  .setName('Margin per users') /*name of the rule list*/
  /* Rules for user concatenated name */
  .addRule('first_name', '$.first_name')
  .addRule('last_name', '$.last_name')
  .addRule('user')
  .thenConcat('User ')
  .thenConcatSymbol('first_name')
  .thenConcat(' ')
  .thenConcatSymbol('last_name')
  /* Rules for email */
  .addRule('email', '$.email')
  .thenSplit('@', '***@$2')
  /* Rules for active field*/
  .addRule('active', '$.active')
  .thenApplyMap({'true': 'ACTIVE', 'false':'INACTIVE'});
  
  return transformerRuleList;
}

The .fromJSON() is here to indicate that we will parse a JSON source. We would use .fromXML() in case of an XML source.

Then the .setName()is used to give a name to the rule list.

For the rules themself, the logic is always to add a new rule with .addRule() where we give the resulting name and then where the value is coming from in the source. For example .addRule('first_name', '$.first_name').

After the .addRule(), we can add one or several .thenSomething method to further modify the value, like for example concatenate with .thenConcat, map with .thenMap and so on. You can find the full list in the API documentation.

executing the transformation

Now that the rules are defined, we can create a TransformerDefinitionand start the transformation with Transformer.

// Create a transformer definition and use the rule list and the record path 
var path = '$.result.*';  // all the records in the JSON are below "result"
var transformerDefinition = new sn_tfrm.TransformerDefinition(getTransformerRuleList(), path);
 
// Instantiate the transformer object.
var transformer = new sn_tfrm.Transformer(transformerDefinition, responseBody); 

// Transform all the records of the source, push them in array results and display the the row
var results = []; 
while (transformer.transform()) { 
  var row = transformer.getRow() 
  results.push(row);
  gs.info(JSON.stringify(row));
}

transform()is executing the transaction for the next row and getRow() retrieve the row, in the form of an object with key-value pairs, as defined in the rule list.

Calculation failure

I wanted to explore the calculation functions provided and see how it works. Unfortunatly I couldn’t make them work when involving two differents elements from the source.

For example, in order to play with the calculations, I added some fields to the user table:

Two currency fields
- u_invoiced_price_per_hour
- u_cost_per_hour
and on integer field:
- u_hours_by_day

Then I tried to calculate the margin price for a user by substracting the u_cost_per_hour from u_invoiced_price_per_hour.

According the documentation it can be achieved by adding the following code to the getTransformerRuleList function:

/* Rules for margin */
.addRule('margin', '$.u_invoiced_price_per_hour')
.thenSubtract('$.u_cost_per_hour');

Unfortunatly it doesn’t work and return errors like this one:

Evaluator: java.lang.NumberFormatException
   Caused by error in script at line 21

     18: } 
     19: 
     20: function getTransformerRuleList () {
==>  21:     var transformerRuleList = new sn_tfrm.TransformerRuleList()
     22:         .fromJSON() /* indicate it is from JSON*/
     23:         .setName('Margin per users') /*name of the rule list*/
     24:         /* Rules for user concatenated name */

java.math.BigDecimal.<init>(BigDecimal.java:497)
java.math.BigDecimal.<init>(BigDecimal.java:827)
com.glide.transform.adapter.CalculationAdapterRule.<init>(CalculationAdapterRule.java:222)
com.glide.transform.adapter.CalculationAdapterRule.createMultiplicationAdapterRule(CalculationAdapterRule.java:60)
com.glide.transform.transformer.AdapterRuleBuilder.thenSubtract(AdapterRuleBuilder.java:78)
com.glide.transform.transformer.js.TransformerRuleList.jsFunction_thenSubtract(TransformerRuleList.java:85)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
java.lang.reflect.Method.invoke(Method.java:498)
...

I try various syntaxes, I try adding a parseInt()and so on, but I couldn’t make it work.

The only way it work was to replace the '$.u_cost_per_hour' by a number, like in the code below, but this would not by useful in our case.

/* Rules for margin */
.addRule('margin', '$.u_invoiced_price_per_hour')
.thenSubtract(15); /*.thenSubtract('$.u_cost_per_hour');*/

Alternative

Bases on this tests, I have the feeling that simply parsing the JSON and performing the necessary transformation directly would be easier and more flexible.

For example, we can achieve the same without using the Transformation Service Plugin with a script like this one:

// parse the response
var responseObj = global.JSON.parse(responseBody);

// get the records/rows inside the "result"
var records = responseObj["result"]

var altResults = [];

// Loop all records[i]
for (var i = 0; i < records.length; i++){
    var resultObj = {};
    resultObj.first_name = records[i].first_name;
    resultObj.last_name = records[i].last_name;
    resultObj.user = "User " + records[i].first_name + " " + records[i].last_name;
    // email
    var email = "";
    if (records[i].email.indexOf('@') > -1 ){
        email =  "***@" + records[i].email.split('@')[1];
    }
    resultObj.email = email;
    // active field
    var active = "ACTIVE";
    if (!records[i].active){
        active = "INACTIVE";
    }
    resultObj.active = active;
    
    // margin
    invoicePriceHour = parseInt(records[i].u_invoiced_price_per_hour);
    resultObj.u_invoiced_price_per_hour = invoicePriceHour;

    costHour = parseInt(records[i].u_cost_per_hour);
    resultObj.u_cost_per_hour = costHour;
    
    resultObj.margin = invoicePriceHour - costHour;

    altResults.push(resultObj);
    gs.info(JSON.stringify(resultObj));
}

It is quite straightforward and easy to follow for any ServiceNow developper.

Performances

Also it seems that using Transformation Service Plugin is a bit slower than the JSON parsing alternative.

To figure out how much slower exactly, I added some code to record the time before and after the “transformer” script and the “parser” script and calculate the duratio in milliseconds. Here are the results:

transfomerStart=1592997620426, transfomerEnd=1592997620556, total=130
parserStart=1592997620556, parserEnd=1592997620581, total=25

I ran it several time and get always similar results, with the “transformer” script running about 5 times slower the the “parser” script.

Conclusions

My guess is that it will still be improved and the issues will resolved, but for the time being, unless there is a very specifc reason, I would avoid using this plugin.

For example, it is a pitty that I could not make the calculation work. I odn’t know if I am doing something wrong or if the functionnality is somehow broken, but this would be very usefull.

It is not possible to handle conditional cases by setting values based on the values of other fields.

Also when performing mapping, there is no way to reject the value in case it is not in the mapping definition. similarly, there is no possibility to use a default value in such case.

In my opinion, it is (still?) not as flexible and versatile as using a JSON (or XML) parser directly and adding the necessary code to perform all the concatenations, controls, mapping and calculation directly.

However, I am speaking from my point of view and having in mind the transformations I need to perform regularly. But there should be use cases where this plugin is usefull and where it would be advantageous to use it, but I am currently not aware of them.

References

⚠ When I last checked I noticed that the current documentation for the API is not 100% accurate. For example .fromJSON() and .fromXML() are not detailed (they are just mentionned in the introduction) and in the examples there is sometime methods that doesn’t exist, like a .addMultiply. Also the examples involving calculation using elements of the source are not working well, as we saw above.

ServiceNow documentation, Transformer API, accessed 2020 06 22
ServiceNow documentation, TransformerDefinition API, accessed 2020 06 22
ServiceNow documentation, TransformerRulesList API, accessed 2020 06 22
complete script snippet can be accessed here

Transformation Service Plugin¶

Overview of the API¶

Test case¶

Transformation rules¶

executing the transformation¶

Calculation failure¶

Alternative¶

Performances¶

Conclusions¶

References¶