Integration: super fast mapping using recursion

[juan9]

Introduction

When creating an integration piece, we usually have to map out all the fields in two places, data source and transform map.

Today we will go over how to flatten out a JSON object so that we can skip the mapping in the data source part of the integration.

Confusing? No worries, here is a small sample of the input and output to expect after reading this article.

INPUT EXAMPLE

var data =  

[

    {

      "location name" : "charlies factory",

      "location properties": {

        "roof": true,

        "fire insurance": false

      },

      "location employees":[

        {"first name": "willy", "last name" : "wanka"},

        {"first name": "lebrun", "last name" : "james"}

      ]

    },

    {

        "location name" : "miami",

        "location properties": {

          "roof": false,

          "fire insurance": true

        },

        "location employees":[

          {"first name": "donald", "last name" : "duck"},

          {"first name": "monkey", "last name" : "pox"}

        ]

      }

]

OUTPUT EXAMPLE

{

  '0': {

    'location name': 'charlies factory',

    'location properties': { roof: true, 'fire insurance': false },

    'location employees': [ [Object], [Object] ]

  },

  '1': {

    'location name': 'miami',

    'location properties': { roof: false, 'fire insurance': true },

    'location employees': [ [Object], [Object] ]

  },

  '0.location name': 'charlies factory',

  '0.location properties': { roof: true, 'fire insurance': false },

  '0.location properties.roof': true,

  '0.location properties.fire insurance': false,

  '0.location employees': [

    { 'first name': 'willy', 'last name': 'wanka' },

    { 'first name': 'lebrun', 'last name': 'james' }

  ],

  '0.location employees.0': { 'first name': 'willy', 'last name': 'wanka' },

  '0.location employees.0.first name': 'willy',

  '0.location employees.0.last name': 'wanka',

  '0.location employees.1': { 'first name': 'lebrun', 'last name': 'james' },

  '0.location employees.1.first name': 'lebrun',

  '0.location employees.1.last name': 'james',

  '1.location name': 'miami',

  '1.location properties': { roof: false, 'fire insurance': true },

  '1.location properties.roof': false,

  '1.location properties.fire insurance': true,

  '1.location employees': [

    { 'first name': 'donald', 'last name': 'duck' },

    { 'first name': 'monkey', 'last name': 'pox' }

  ],

  '1.location employees.0': { 'first name': 'donald', 'last name': 'duck' },

  '1.location employees.0.first name': 'donald',

  '1.location employees.0.last name': 'duck',

  '1.location employees.1': { 'first name': 'monkey', 'last name': 'pox' },

  '1.location employees.1.first name': 'monkey',

  '1.location employees.1.last name': 'pox'

}

PRE-REQUIREMENTS FOR SERVICENOW

Activate the integration hub plugin 

CREATE AN ACCOUNT WITH NY-TIMES && GET ACCOUNT KEY

The first thing we need to do is set up the authentication piece

Create NY-dev account and instructions on getting token

https://developer.nytimes.com/get-started

Api URL Info can be found here: 

https://developer.nytimes.com/docs/articlesearch-product/1/overview

The API we are going to be hitting is :

https://api.nytimes.com/svc/books/v3/lists/current/hardcover-fiction.json?api-key=YOUR_KEY

MY_KEY = X4rD4hZgxGtoJ5CcmnNCJRnGk6n8GGOM (NOTE: this key might not be working by the time you use it)

CREATE AN ACTION IN SERVICENOW

in servicenow

go to integration hub > action designer

Click 'Actions'

Click 'New'

Click 'Action'

Input a name

Click 'Submit'

Create a 'REST step #1'

Set the following::

Connection = Define Connection Inline

Base URL = https://api.nytimes.com

Resource Path = /svc/books/v3/lists/current/hardcover-fiction.json

Query parameters: api-key = YOUR-KEY

Create a 'Script step #2'

Set the following::

status_code = < status_code pill >

response_body = < response_body pill >

script = 

(function execute(inputs, outputs) {

    if (inputs.status_code == "200") { 

        var responseBody = JSON.parse(inputs.response_body);        

        outputs.summarystring = ""+inputs.response_body;      

    }

})(inputs, outputs);

Output Variables

summarystring [string]

Update the Action Output to the following

-summarystring [string]

Click 'Test'

Click 'Run Test'

Click 'Your test has finished running. View the action execution details.'

Under the 'Action' section, you should see 

{"status":"OK","copyright":"Copyright (c) 2022 The New York Times Company. All Rig....

Open the action previously created, make sure that the action is published,

Click on the three dots at the top right of the screen

Click 'Create code snippet'

Click 'Copy Code Snippet to Clipboard'

Now that we have created the action, we will create a data source!

CREATE AN ACTION IN SERVICENOW

in servicenow

go to System import set > Data source

Click 'New'

Set the following::

Name = really fast import mapping

Import set table name = <filled out by system>

Type = Custom (load by Script)

Save the record.

Data Loader:

(function loadData(import_set_table) {

    function flattenJSON(obj, res, extraKey) {

        /*

         flatten out json object while keeping all relations

         obj: obj passed to be flattened

         res: flatten version on obj

         extrKey: key that allows the relationship to be maintained

        */

        if (obj == null) obj = {};

        if (res == null) res = {};

        if (extraKey == null) extraKey = '';

        for (key in obj) {

            res[extraKey + key] = obj[key];

            if (typeof obj[key] === 'object') {

                flattenJSON(obj[key], res, extraKey + key + ".");

            }

        }

        return res;

    }

    function getAndLoadData() {

        try {

            var result = sn_fd.FlowAPI.getRunner().action('x_337431_mini_inte.hit_api').inForeground().run(); // replace with correct action scope/name

            var outputs = result.getOutputs();

            var summarystring = outputs['summarystring']; // String

            var objTargetData = JSON.parse(summarystring)['results']['lists'][0]; // target only a bit of the data that we are pulling

            import_set_table.insert(flattenJSON(objTargetData));

        } catch (ex) {

            var message = ex.getMessage();

            gs.error(message);

        }

    }

    getAndLoadData();

})(import_set_table);

Click 'Load All Records'

Done!

The staging table has been created with all flattened properties (looks a bit ugly, you can cherry-pick how to map the fields in the transformed piece)

Here is the link to all the code

https://github.com/casas1010/servicenow-mini-integration

Connect with me on LinkedIn for questions or comments

https://www.linkedin.com/in/juanfcasas/