Tidying Data

Overview

Copy

As mentioned in the Intro to managing data, you will frequently find that certain service connectors will return large reams of nested JSON output which will be tricky to get at.

It is important to make this data manageable.

It is particularly important if you are wanting to pull in information on certain records from multiple services - all of which may return data in a complex nested format. You may, for example, have information on a contact stored in both Hubspot and Salesforce. The answer here is to pull this information into one 'object' which contains only the data you need.

An example of a service which returns data in a complex format is the AWS SQS connector which returns message data in the following format:

Or the Hubspot Find Contact by ID operation which includes a long list of nested analytics data in its output:

Tidying data with the Object Helpers connector

Copy

With the above Hubspot example, if the only data we want to make available for subsequent connectors and services is the firstname, lastname and jobtitle of the contact, we can pinpoint this and create an object using the Object Helpers Add key/value pairs operation:

From the screenshot above you can see that the operation has created a property for the source of type = contact and it has created key/value pairs for each of the properties we are interested in by using the jsonpaths required to extract them from the Hubspot step output, i.e.:

• $.steps.hubspot-1.properties.firstname.value

• $.steps.hubspot-1.properties.lastname.value

• $.steps.hubspot-1.properties.jobtitle.value

Note that the exact jsonpath will depend on the connector. To help find the jsonpaths for that Hubspot operation we scrolled down to use the output schema:

When the workflow is run you will now see that the output of the Object Helpers has produced a clean and simple object:

It will now be very simple for any subsequent steps to get at this data.

In more complex workflows, you could also pull in key/value pairs from other services (e.g. Salesforce) to build your object, i.e. you may have information stored on a contact in more than one service.

Tidying data with the Script connector

Copy

The above could also be achieved with the Script connector.

All you would need to do is use the same jsonpaths to set the Variables for your script:

And then reference these variables by entering a script into the Script box:

1
exports.step = function(input) {
2
    return {
3
    "contact": {
4
      "firstname": input.firstname,
5
      "lastname": input.lastname,
6
      "jobtitle": input.jobtitle
7
      }
8
     }
9
};

This then outputs the contact in a similar fashion to the Object Helpers connector: