Transforming data

As data is passed through a flow, the format of the data may need to be modified so that the Receiver component can recognise and use it. A simple data mapping process may be enough, however in some cases you may need to make more complex changes such as changing date formats, calculations, or concatenating fields. This is done by "transforming" the data,

In this section we will discuss some JSONata basics so that you can start transforming your data. Note that these are examples only, there is detailed information about how to use JSONata available online for free.

Learn how to manipulate JSON using JSONata in Integrations.

Transforming strings

JSONata expressions are used to carry out transformations with strings using standard string operators. More complex string functions like $uppercase(str), $lowercase(str), $split(str, separator [, limit]), $joinarray[, separator] can also be used.

For the following examples we will assume the following incoming data:

{
  "author":"Mark Twain",
  "fname":"Tom",
  "lname":"Sawyer",
  "email":"tom.sawyer@twaincreations.com",
  "town":"StPetersburg",
  "state":"Missouri"
}

Concatenation

To start with a simple example let us concatenate parts of the incoming data to a single string - in this case the title of the book:

"The Adventures of " & fname & " " & lname

Here we used the & operator to combine the strings into one message: The Adventures of Tom Sawyer.

Changing text to caps

Now we want to write the same sentence in all caps since it is the title of a book:

$uppercase("The Adventures of " & fname & " " & lname)

The result is: THE ADVENTURES OF TOM SAWYER.

Line breaks and further concatenation

Now we will add the name of the author in the next line:

$uppercase("The Adventures of " & fname & " " & lname)
& "\n" & $uppercase("by " & author)

And the outcome is:

THE ADVENTURES OF TOM SAWYER
BY MARK TWAIN

Get a domain name from an email address

What if we want to get the domain name from the incoming email address tom.sawyer@twaincreations.com? To do that we could write the following JSONata expression:

$split(email, /[@,.]+/)[2]

This expression first takes the value of the email field, splits it using the @ and . characters to build an array with elements containing ["tom","sawyer","twaincreations","com"]. Then it takes the domain name value, which is the element [2], to get the final result of twaincreations .

Check whether a string contains a field

If you noticed the email address domain contains the author’s surname. Can we be sure?

$contains(email,$lowercase($split(author,"")[1]))

This JSONata expression takes the value of the author field, splits it into an array and takes the surname part (Twain). Then it converts it to lower case and uses the value (twain) to check the email field for a presence of a value. The answer is true.

Performing simple numeric transformations

Turning a string into a number

Before starting with examples of numeric calculations and transformations, let’s look at the simple function $number(arg), which solves many data issues by turning a string into a number that can be used in calculations.

Let us consider the following incoming data sample:

{
  "itemPrice": {
    "amount": "25.44",
    "currencyCode": "USD"
  }
}

The value of the itemPrice.amount parameter is not a number but a "25.44", which is a string. As it stands we would not be able to use it in any numeric calculations. To address this we could write:

$number(itemPrice.amount)

This would result in 25.44, which is a number.

Changing the format of a number

Many European systems express decimals using a comma instead of a point. Before converting a string to a number in this case you need to change the format:

{
  "itemPrice": {
    "amount": "12,99",
    "currencyCode": "EUR"
  }
}

Before we pass the value itemPrice.amount to $number(arg) function we need to replace the comma with a point. Here we will use a string transformation function $replace(str, pattern, replacement [, limit]):

$number($replace(itemPrice.amount, ",","."))

The result would be 12.99, which is a number.

Numeric calculations

Basic arithmetic

Now let’s perform some basic arithmetic operations on numbers in your payload. Consider the following data was produced by a source system:

{
  "orderItems": [
    {
      "conditionId": "New",
      "promotionDiscount": {
        "amount": 1.99,
        "currencyCode": "USD"
      },
      "giftWrapPrice": {
        "amount": 0.00,
        "currencyCode": "USD"
      },
      "shippingPrice": {
        "amount": 4.49,
        "currencyCode": "USD"
      },
      "itemPrice": {
        "amount": 25.44,
        "currencyCode": "USD"
      },
      "quantityShipped": 2,
      "title": "my life in kenya"
    }
  ]
}

We need to do some basic maths to summarise the shipment title and cost and express it as a JSON. In the following example the Shipping cost is charged in full for each item purchased, and the discount is applied once:

{
  "shipment title" : orderItems.title,
  "shipment price" : orderItems.(quantityShipped*(itemPrice.amount + shippingPrice.amount) - promotionDiscount.amount)
}

In this example we

Added the itemPrice.amount to shippingPrice.amount
Multiplied the total by the quantityShipped value
Then applied the promotionDiscount.amount

In numbers that would be 2 * (25.44 + 4.49) - 1.99. Here is the outgoing JSON:

{
  "shipment title": "my life in kenya",
  "shipment price": 57.87
}

Using numeric aggregation functions

For this example let us assume we want to apply the promotionDiscount.amount to each item in the order instead of just once.

{
  "shipment title" : orderItems.title,
  "shipment price" : orderItems.(quantityShipped*$sum([itemPrice.amount, shippingPrice.amount, -promotionDiscount.amount]))
}

Here is the outgoing JSON:

{
  "shipment title": "my life in kenya",
  "shipment price": 55.88
}

Notice we have used $sum(array) which is a numeric aggregation function along with $max(array), $min(array) and $average(array).

These are standard JSON functions which you can research and apply as needed in your transformations.

Transforming dates and times

Extensions such as moment.js can be used as per the below examples. Again there is a wealth of further reading available for free online.

Managing timezones

You can use $moment() to return the current timestamp (equivalent to $now()).

$moment()

If the local time is 12.00pm (Australian Eastern Daylight Time), the output in ISO 8601 standard format will be

2020-04-06T07:12:00+0000

To convert the date explicitly to UTC (11 hours behind AEDT) use:

$moment().utc()

Date formats

Consider the following date:

$moment('06.04.2020')

The outcome may be read in two ways:

6th of April 2020
4th of June 2020

To ensure the date is managed correctly you can mandate how the date format is received:

// String + format
$moment('06.04.2020','DD.MM.YYYY')
// or ISO 8601
$moment('2020-04-06')

Both implementations will return the same value:

"2020-04-06T07:12:00+0000"

Formatting dates and other manipulations

There is a wide range of reading available on how to use JSON to modify dates. When using a Transformation in Connect you can apply any of these JSON functions.

Avoiding the common pitfalls of date parsing

While building integration flows using JSONata mapper expressions it is recommended that you test your expressions carefully. the two biggest pitfalls are:

The native parsing of dates in different browsers is very inconsistent. If one expression is parsed correctly in Google Chrome it might not be parsed in Apple Safari at all.
The time zones and offsets play a significant role. If the developer and client are in different timezones for example, or if data is being received from multiple timezones test carefully that timezones and daylight savings offsets are working correctly.

Working with arrays

Selecting elements in arrays

Consider the following array.

{
  "Order": [
    {"ids":[1,2,3]},
    {"ids":[4,5,6]},
    {"ids":[7,8,9]}
  ]
}

To select the first embedded array elements ([1,2,3]) use:

Order[0].ids

To select only the first element of the first embedded array (1) use:

Order[0].ids[0]

It is possible to select elements using a wildcard * like:

*[0].*[0]

Returning just 1 like the previous example.

Flattening arrays

Considering the same input array example, here is how to flatten the embedded arrays into one:

Order.ids

Which would result in: [1,2,3,4,5,6,7,8,9]

In reality you may have the following JSON input from an online order which you need to simplify into a JSON document that summarises the order. This example includes:

defining the structure of the the JSON
performing calculations
flattening nested arrays

{
  "Account": {
    "Account Name": "Firefly",
    "Order": [
      {
        "OrderID": "order103",
        "Product": [
          {
            "Product Name": "Bowler Hat",
            "ProductID": 858383,
            "SKU": "0406654608",
            "Description": {
              "Colour": "Purple",
              "Width": 300,
              "Height": 200,
              "Depth": 210,
              "Weight": 0.75
            },
            "Price": 34.45,
            "Quantity": 2
          },
          {
            "Product Name": "Trilby hat",
            "ProductID": 858236,
            "SKU": "0406634348",
            "Description": {
              "Colour": "Orange",
              "Width": 300,
              "Height": 200,
              "Depth": 210,
              "Weight": 0.6
            },
            "Price": 21.67,
            "Quantity": 1
          }
        ]
      }
    ]
  }
}

To create a custom JSON document on output follow these guidelines:

Include the output in curly brackets { }
JSON property names can be declared:

{"parameter": Account.Order[0].OrderID}

which returns:

{"parameter":"order103"}

To access the properties with space in the name use brackets:

{"name": Account.Order[0].Product[0]."Product Name"}

returns:

{"name": "Bowler Hat"}

To refer the value of "Product Name" property in an embedded array structure use $ to reference the current array level:

{"product": Account.Order.Product.({"name" : $."Product Name"})}

which would return

{
  "product": [
    {"name": "Bowler Hat"},
    {"name": "Trilby hat"}
  ]
}

Without ```suggestion:-0+0 the value of `"Product Name" would not have been propagated.

Following the above guidelines, here is the final JSONata expression:

{
  "account": Account."Account Name",
  "orderID": Account.Order.(OrderID),
  "products": Account.Order.Product.({
    "name": $."Product Name",
    "revenue": (Price * Quantity)
  })
}

It returns the following JSON document:

{
  "account": "Firefly",
  "orderID": "order103",
  "products": [
    {
      "name": "Bowler Hat",
      "revenue": 68.9
    },
    {
      "name": "Trilby hat",
      "revenue": 21.67
    }
  ]
}

Help Center

Menu