Skip to content

Json Actions

The JSON Actions allow Automations to create, read, update, and transform JSON data. These actions are useful when working with APIs, structured datasets, CRM/web service responses, database records, or any workflow that needs to build or manipulate JSON content. JSON values can be dynamically assigned using %variables%, making it easy to construct structured output, update specific JSON paths, extract fields, or convert JSON into formats such as HTML for reporting or email output. JSON Actions help you integrate ThinkAutomation with any system that exchanges data using JSON.

Create JSON

Create Json text and assign variables to each Json element. The resulting Json text can be assigned to a variable for use on subsequent actions.

You first need to enter or paste valid Json text in the Enter Or Paste Json Here To Parse entry. You need to obtain the Json schema that you want to update and copy/paste into this entry. Then click the Parse > button.

The Json will be parsed and all elements will be listed in a tree structure.

Select each line in the tree in turn. The Selected XPath, Element and Type will be shown as you select each entry.

In the Set To Value entry enter or specify the data that you want to be assigned to the selected entry. Fields/Variables can be specified here. Then click the Update button. The updated Json will be shown in the Preview Result view. Repeat for each element that you want to update.

In the Assign Json To list select the variable you want to resulting Json assigned to. If the Compact option is enabled then the Json will be returned in compact format, otherwise it will be formatted and indented.

When the message is processed each element in the Json will be assigned the value you specify and the resulting Json assigned to the specified variable.

This Json can then be used on other actions by referencing this variable.

You can also use the Update Json and Set Variable actions to manually create Json. The Set variable text can contain %variable% replacements. When ThinkAutomation replaces values during Automation execution it checks if the value is being replaced inside Json - and if so the replaced value will be Json 'escaped'. See: Json Notes.

Update JSON

Update Json text. Create or update multiple Json paths within Json text and return the updated Json to a variable.

In the Existing Json entry, enter any valid json text or use a %variable% replacement containing Json. If this entry is blank a new Json document will be created.

You can now use the Set Paths grid to update specific Json paths. If the specified path already exists, it's value will be updated with the value in the Set To column. If the path does not exist it will be added.

Note: Paths are JsonPath format which uses dot notation and array indexes starting at zero (eg: name.Department). This differs from the Create Json and Read Json Document actions which use XPath notation.

The Set To column can contain fixed value or %variable% replacement or combination.

The Type column can be used to force the Json type at the specified path. If the Type is set to Auto or blank then the type will be automatically chosen based on the Set To value.

For example, suppose we have the following Json:

{
    "Name": "",
    "StartDate": "",
    "Age": 0,
    "Started": true,
    "Department": {
      "Name": "",
      "Manager": ""
    }
}

If the Set Paths are set to:

Path Value Type
Name Nathan Walters String
StartDate %Msg_Date% DateTime
Age 30 Number
Department.Name Marketing String
Department.Manager Howard Williams String

The resulting Json would be returned as:

{
  "Name": "Nathan Walters",
  "StartDate": "2021-10-11T15:52:36Z",
  "Age": 30,
  "Started": true,
  "Department": {
    "Name": "Sales",
    "Manager": "Howard Williams"
  }
}

If the Existing Json was blank it would be returned as:

{
  "Name": "Nathan Walters",
  "StartDate": "2021-10-11T15:56:53Z",
  "Age": 30,
  "Department": {
    "Name": "Sales",
    "Manager": "Howard Williams"
  }
}

Since all the paths are new - and so all the Set Paths will be added.

Paths use dot notation to reference nested objects. You can also use [index] to reference specific array items (eg: object.nestedobject.array[1].value). Index numbers start at zero.

Paths are case sensitive.

Removing Paths

You can also use this action to remove paths from existing Json. In the Remove Paths grid enter the paths that you want to remove.

To remove individual array items the path must end with the index enclosed in square brackets (eg: object.array[1]). Array indexes start at zero. When removing multiple array items from the same array the index will change after each deletion. So to remove the first two array items you would set the Remove Paths to:

object.array[0] ' after this deletion the second item will be move to 0
object.array[0] ' so we need to delete zero again

Assign To

Select the variable to receive the updated Json from the Assign To list. If the Compact option is enabled then the Json will be returned in compact format, otherwise it will be formatted and indented.

Read JSON Document

Parses a Json formatted document from any URL or text/variable and assigns element values to variables.

Parsing Json From Web Responses

Select the Get Json From URL tab to read a Json document from a URL.

Enter the Json Document URL to read. This can be a secure HTTPS URL if required.

If the web resource requires authentication then specify the Authentication method and optionally a User Name/Password or an OAuth Auth Token retrieved from a previous OAuth SignIn action.

The HTTP status code can be assigned to a variable from the Assign Status Code To list.

The full Json text returned from the URL can be assigned to a variable. Select from the Assign Returned JSON To list.

You can also map specific path values to ThinkAutomation variables by entering the Full Path and selecting the variable from the Assign Value To column.

The Full Path entries used when extracting specific attributes can contain %variable% replacements.

Note: The paths used are XPath notation.

Click the Test button to parse the response from current URL or text. This results will be shown in the tree. Click each node to expand the child nodes. The Selected Path & Selected Value entries will show the value for the currently selected Path. You can copy/paste the Selected Path into the Assign grid.

Parsing Json Text

You can also specify any Json text directly instead of reading from a URL. This can be a variable from a previous action. Select the Json Text tab.

Enter or paste the Json text or specify a %fieldname%. When developing your Automation you can paste some sample Json text into the Json Text entry & click Test to parse and view the paths. Then remove the sample Json text or replace it with a %variable% replacement.

Convert JSON To HTML

Converts any Json text to a HTML table.

This action is useful when you read Json data from a database, web resource or web API etc., and you need to easily convert the content to readable HTML. The HTML can then be included on outgoing emails or used as a return value for webform Message Sources.

In the Json editor, enter or paste the Json text, or use a %variable% replacement for Json created from an earlier action.

The Preview window will show a preview of the HTML created. The Json will be converted to a HTML table. Sub-objects/arrays etc will be converted into nested tables.

You can optionally add a Title which will appear above the table.

Enable the Convert Property Names option to convert camelCase property names into separate words. For example: customerName will appear as Customer Name.

If the Return Full Page option is enabled then the returned HTML will include html, body, head tags. Otherwise only the table html will be returned. This is useful if you are embedding the table into an existing HTML document.

If the Use Default Styles option is enabled then the table will have a default style sheet applied.

You can also optionally add any custom CSS (eg: body {background-color: lightblue;}).

Select the ThinkAutomation variable to receive the HTML from the Assign Html To list.

Example:

The following Json:

{
    "Name": "ABC Limited",
    "EmailAddress": "a.dutchess@abclimited.com",
    "Address": {
        "AddressType": "POBOX",
        "AddressLine1": "P O Box 123",
        "City": "Wellington",
        "PostalCode": "6011",
        "AttentionTo": "Andrea"
    },
    "Currency": "NZD"
}

Would be converted to:

<table>
    <tbody>
      <tr>
        <td><b>Name</b></td>
        <td>
          ABC Limited
        </td>
      </tr>
      <tr>
        <td><b>Email Address</b></td>
        <td>
          <a href='mailto:a.dutchess@abclimited.com' target='_blank'>a.dutchess@abclimited.com</a>
        </td>
      </tr>
      <tr>
        <td><b>Address</b></td>
        <td>
          <table>
            <thead>
              <tr>
                <th>Address Type</th>
                <th>Address Line1</th>
                <th>City</th>
                <th>Postal Code</th>
                <th>Attention To</th>
              </tr>
            </thead>
            <tbody>
              <tr>
                <td>POBOX</td>
                <td>P O Box 123</td>
                <td>Wellington</td>
                <td>6011</td>
                <td>Andrea</td>
              </tr>
            </tbody>
          </table>
        </td>
      </tr>
      <tr>
        <td><b>Currency</b></td>
        <td>
          NZD
        </td>
      </tr>
    </tbody>
  </table>