Open Navigation

Working with data and jsonpaths

Using jsonpaths to map data between steps

As discussed in the intro to data and jsonpaths, data in the Tray platform is returned in JSON format.

When you are wanting to 'pull' data from one service in order to 'push' it into another, you can do this using 'jsonpaths'.

Note: The quickest and simplest way to work with jsonpaths is to use the 'connector snake' and 'dynamic output schema' as explained in the page on jsonpath shortcuts. However, it is important to understand how jsonpaths work so you are fully in control of your data. So please read on! Once finished please also see our section on managing data for some best practices on managing your data in Tray.io workflows.

A jsonpath is a standardized way of writing a single line of text which can extract values from fields, objects and arrays contained in JSON data.

All jsonpaths will begin as follows:

  • If the data is coming from the trigger step of your workflow it will begin with $.steps.trigger
  • If the data is coming from a service connector step in your workflow it will begin with a path which names the service such as $.steps.slack-1 or $.steps.marketo-2
  • If the data is coming from a core connector step it will be something similar to $.steps.script-2 or $.steps.loop-3
  • If the data is coming from a helpers connector step it will be something similar to $.steps.text-helpers-1 or $.steps.list-helpers-1

The following illustration shows how you can determine the prefix for a jsonpath by looking at the connector name and number under the step title for the connector you are wishing to pull data from:

steps-numbers-table

Pulling data from a trigger

To show how to create complete jsonpaths we can use an example workflow which makes use of the same sample data shown in the intro to data and jsonpaths:

jsonpath-workflow

In this workflow, we have data about an individual which has been received via a webhook trigger:

  1. The workflow is triggered by data being received about a new lead which needs added to Salesforce (in this case it is a webhook trigger)
  2. The Salesforce step uses the Create New Record (Lead) operation to add the lead to Salesforce
  3. To populate the First Name, Last Name and City values the Salesforce step uses jsonpaths to extract the relevant data from the trigger step

From the screenshot you can see that the details of the individual are contained within a body object, and that to access:

  • firstName we use $.steps.trigger.body.firstName
  • lastName we use $.steps.trigger.body.lastName
  • city we use $.steps.trigger.body.address.city

Note that the data is case-sensitive, so accuracy is crucial here - e.g. 'firstname' will not work, because it must be 'firstName'.

Also note that you must change the field type to jsonpath for each value:

choose-jsonpath-dropdown

Accessing data in arrays

The above examples are quite straightforward. However, if we want to access the correct value to populate Mobile Phone in Salesforce, it is slightly different, as John's phones are stored in an array within phoneNumbers, as indicated by the use of [ ] in the output data.

jsonpath-array

From the above screenshot you will see that the correct jsonpath for this is $.steps.trigger.body.phoneNumbers[0].number

This is because we have identified that, in order to populate Mobile Phone in the Salesforce record, the correct phone number from the list is type iPhone (i.e. not 'home').

In order to pick the first item from an array we use [0] as in the above jsonpath.

Why do we use [0]? Because arrays returned in Tray platform data are always zero-indexed!

So in this case, if we wanted to access the second item in this list (i.e. the 'home' phone number) we would use [1]

So when accessing data / objects in arrays, you use:

  • [0] to get item number 1
  • [1] to get item number 2
  • [2] to get item number 3
  • etc.

Pulling data from service connectors

The following example shows how you can use jsonpaths to pull data from a service connector:

sf-to-clearbit-jsonpath

In this case we have used Salesforce to return the Id and Email of a lead, which we would like to pass into a Clearbit connector step.

From the output on the left side of the screen you can see that the lead is the first (and only!) result in a records array of results (indicated by the [ ]).

Therefore (remembering the rules on accessing data from arrays noted above) the jsonpath we use to pull the email which is needed for the Clearbit Enrichment operation is:

$.steps.salesforce-1.records[0].Email

Note that the exact path for this might change depending on the position of your Salesforce connector in your workflow. For example, it could be $.steps.salesforce-3.records[0].Email if it was the third Salesforce connector you added to the workflow.

Pulling data from core / helper connectors

You can also pull data from Tray platform [core] and helper connectors.

For example, we could use $.steps.script-1.result.messages[0].body to get at the output of a script connector step such as:

script-connector-example-output

Or we could use $.steps.storage-1.value to pull data from a data storage connector step.

Pulling data from a loop connector

When building workflows, it is very common that you will need to use the Loop Connector to go through a list of results one-by-one. In this case you will then be adding further connector steps within the loop which will pull the data from each result using jsonpaths which begin with e.g. $.steps.loop-1.value

For detailed instructions on how to do this please see the Loop Connector documentation page. Also see the note on manually setting the dynamic output schema

Interpolated mode

It's also possible to do more advanced mapping using interpolated mode. Interpolated mode is used when the destination input requires a string and can do things like:

  • Concatenate multiple variables into one input, for example "first name" and "last name" both into a name field
  • Mix one (or more) variables with other static text, for example adding some data driven details to the body of a standard email template.
  • Converting other types into a string, for example numbers and booleans can be converted into a string by setting the input to just the interpolated variable.

To pass variables in interpolated mode, wrap the JSONPath variable in curly bracket and use it anywhere within the input, like so: {$.steps.STEPNAME} (Note: The input property type should always be "string")

For example, if you were naming an Asana project based on the name of a Salesforce record you could use: My new project: {$.steps.salesforce-1.records[0].Name} as per the following screenshot:

create-project-json-path

Passing variables with spaces

If a variable contains spaces then it must be passed inside ['']

For example a body response such as:

{
"body": {
"Primary User Email": "email_address"
}
}

Would be passed as $.steps.trigger.body['Primary User Email'] if the body had come from the output of the workflow trigger.

Was this article helpful?
Yes
No