Skip to content

Json Notes

Creating Json

Json text can be created using the Create Json, Update Json or Set Variable actions.

The Create Json action is useful where you have a pre-defined Json schema and you want to assign values to specific paths and then return the resulting Json to a variable. It maintains the data type (string, date, boolean, number) of the pre-defined schema.

The Set Variable action allows ad-hoc creation of Json - but you need to add quotes for string values. For example, if we had the following Json:

{
    "Name": "",
    "Email": "",
    "StartDate": "",
    "Age": 0
}

And we wanted to set a value and include %variable% replacements, eg:

{
    "Name": "%Name%",
    "Email": "%Email%",
    "StartDate": "%StartDate%",
    "Age": %Age%
}

Any string %variable%replacements that are strings or dates must be enclosed in quotes.

The Set Variable action has a 'Convert: Reformat Json' operation which will reformat, and re-indent the Json (after the %variable% replacements).

Updating Json

The Update Json action is useful where you want to create or update Json and set the values of specific paths. Any existing paths in the Json that are not updated will retain their existing values. Any paths updated that do not already exist in the existing Json will be added.

Json Variable Replacements

When an Automation executes, any %variable% replacements inside Json text will be correctly 'escaped' - unless the replace value itself is already Json. Any Extract Field values that are set as 'Date' type, or any built-in variables that are dates (%Msg_Date%, %Date% etc.) will be replaced in ISO 8601 format.

Built In Json Variables

%Msg_Json%

This built-in variable will return Json text representing the current message being processed. For example:

{
    "MessageId": "12c1c473-862d-44f1-9dc7-cbf0474fba12",
    "Subject": "Test Subject",
    "Dated": "2021-09-23T10:49:55+01:00",
    "BodyPlainText": "Test Body",
    "BodyHTML": "",
    "From": [
      {
        "Email": "howard.williams@parkersoftware.com",
        "Name": "Howard Williams"
      }
    ],
    "ToAddress": [
      {
        "Email": "stephen@parkersoft.co.uk",
        "Name": ""
      }
    ],
    "CC": null,
    "BCC": null,
    "Sender": null,
    "ReplyTo": null,
    "InReplyTo": null,
    "References": "",
    "ReturnPath": "",
    "MessageHeaders": [
      {
        "Header": "MIME-Version",
        "Value": "1.0"
      },
      {
        "Header": "Date",
        "Value": "Thu, 23 Sep 2021 10:49:55 +0100"
      },
      {
        "Header": "Message-Id",
        "Value": "12c1c473-862d-44f1-9dc7-cbf0474fba12"
      },
      {
        "Header": "Content-Type",
        "Value": "text/plain; charset=us-ascii; format=flowed"
      },
      {
        "Header": "Content-Transfer-Encoding",
        "Value": "7bit"
      },
      {
        "Header": "X-Priority",
        "Value": "3 (Normal)"
      },
      {
        "Header": "To",
        "Value": "stephen@parkersoft.co.uk"
      },
      {
        "Header": "From",
        "Value": "Howard Williams <howard.williams@parkersoftware.com>"
      },
      {
        "Header": "Subject",
        "Value": "=?us-ascii?B?SnNvbiBSZXBsYWNl?="
      }
    ],
    "ContentType": "text/plain; charset=us-ascii; format=flowed",
    "ContentTransferEncoding": "7bit",
    "CharSet": "us-ascii",
    "Language": "latin1",
    "OrigDate": "2021-09-23T10:49:55+01:00",
    "Size": 356,
    "ReadReceipt": false,
    "Importance": "NORMAL",
    "Sensitivity": "NORMAL",
    "Attachments": null,
    "RelatedItems": null,
    "SolutionId": "60e2e90913d7e55818292ddb",
    "MessageSourceId": "613b33bb13d7e531e41aa54f",
    "AutomationId": "614c3f5413d7e531b004b412",
    "MessageStoreId": "613b33bb13d7e531e41aa54f"
  }

%Msg_ExtractedFieldsJson%

This variable will return Json text containing the name and value for each Extract Field action. For example, suppose you have created the following Extract Field actions:

  • From = Extract Field Built-In Variable %Msg_FromEmail%
  • Size = Extract Field Built-In Variable %Msg_Size%
  • Dated = Extract Field Built-In Variable %Msg_Date%
  • Body = Extract Field Built-In Variable %Msg_Body%

The %Msg_ExtractedFieldsJson% built-in variable would return:

{
  "From": "howard.williams@parkersoftware.com",
  "Size": 355,
  "Dated": "2021-09-23T11:00:52Z",
  "Body": "Test Message"
}

You can define the field type for each Extracted Field on the Extract Field action - Database Update Tab - Field Type.

The %Msg_ExtractedFieldsJson% variable is useful when used with the Embedded Data Store action. You can set the Insert json to this variable to insert your extracted fields into an easy to use data store that you can query on from other Automations.

Extracting From Json

There are a number of ways to extract data from Json text.

Extract Field Action

You can use the Extract Field action to extract a specific Json path value. If the Extract Field From value contains Json text then you can enable the Extract Json Path option and then enter or select a path. If you paste some sample Json into the Helper Message the path list will be populated. You can also extract Json array values to a CSV string.

Read Json Document

The Read Json Document action can be used to read Json from a web resource or specify Json Text manually (or a %variable% replacement read from an earlier action). You can then extract multiple paths and assign the values to variables.

InLine Function

The InLine function option allows you to extract a specific Json path value without creating an action. Simply use the InLine function as you would any other %variable% replacement.

You can use the %func%:JsonValue inline function to extract a specific Json path value. For example, if you have a variable called 'AddressJson' set to:

{ 
  "employee": { 
    "name": "John",  
    "salary": 56000,  
    "married": true 
   } 
}

You could use the inline function such as:

Dear %func%:JsonValue(%AddressJson%,"employee.name"),

At runtime the replaced text would be:

Dear John,

Extracting And Looping Through A Json Array

The Extract Field action allows you to extract at a specific Json path. If this path is an array you can enable the Extract All Array Values To CSV option.

For example, suppose you have the following Json:

{
  "Actors": [
    {
      "name": "Tom Cruise",
      "age": 56,
      "Born At": "Syracuse, NY",
      "Birthdate": "July 3, 1962",
      "photo": "https://jsonformatter.org/img/tom-cruise.jpg"
    },
    {
      "name": "Robert Downey Jr.",
      "age": 53,
      "Born At": "New York City, NY",
      "Birthdate": "April 4, 1965",
      "photo": "https://jsonformatter.org/img/Robert-Downey-Jr.jpg"
    }
  ]
}

You could create an Extract Field action and set the Path to Actors and enable the Extract All Array Values To CSV option.

The Extract Field value would be set to:

Tom Cruise,56,"Syracuse, NY","July 3, 1962",https://jsonformatter.org/img/tom-cruise.jpg
Robert Downey Jr.,53,"New York City, NY","April 4, 1965",https://jsonformatter.org/img/Robert-Downey-Jr.jpg

You can then use the For..Each action to loop over each line in the returned CSV and within the loop use the Parse CSV Line action to get specific values.

Looping Through Json Member Values Using For..Each

You can use the For Each action to loop on Json members. For example, the following Json:

{ 
  "employee": { 
    "name": "John",  
    "salary": 56000,  
    "married": true 
   } 
}

In the For..Each action set the For Each to Json Member In and set the Json In to a %variable% containing the Json. With the Start At Path set to employee.

You can assign each member value and name to variables.

The above loop would set the variables:

%name%=name, %value%=John
%name%=salary, %value%=56000
%name%=married, %value%=true

Array example:

["Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"]

Using a For..Each action on the above Json. The values assigned would be 'Sunday','Monday'.. etc.

Array Example 2:

{
    "id": "0001",
    "type": "donut",
    "name": "Cake",
    "ppu": 0.55,
    "topping":
        [
            { "id": "5001", "type": "None" },
            { "id": "5002", "type": "Glazed" },
            { "id": "5005", "type": "Sugar" },
            { "id": "5007", "type": "Powdered Sugar" }
        ]
}

Using a For..Each on the above Json with the Start At Path set to topping. The values in the loop would be assigned:

{
  "id": "5001",
  "type": "None"
}

{
  "id": "5002",
  "type": "Glazed"
}

etc.

If you set the Start At Path to topping[0] then this will iterate through the first array item in the topping array.

Converting Json To HTML

You can convert Json text to human readable HTML using the Convert Json To HTML action.

This action us 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.

Converting Json To XML

You can convert Json text to XML (and XML To Json) using the Set Variable action with the Convert: JSON To XML operation (or Convert: XML To Json) operation.