JSONata

The JSONata Transform Stage is used to configure advanced transformations on incoming JSON using JSONata expressions. During execution, the expression can modify the event value, extract metadata for use in other stages, and incorporate additional information into the event data.

The full specification for JSONata can be found here.

JSONata Stage Transform

Transform Expression

Required JSONata expression.

Using Transform Expressions

The transform expression executes JSONata to modify the event value and/or operate on metadata about the value that can be used in Output stages. Input references (using {{}}) in Pipeline expression fields is not currently supported. JSONata in the transform expression has access to the following objects:

Event

System

Example Transform Expressions

The following are examples of transform expressions.

Aggregation Query Example

In this example, assume the event payload contains the following JSON.

{
  "Account": {
    "Account Name": "Firefly",
    "Order": [
      {
        "OrderID": "order103",
        "Product": [
          {
            "Product Name": "Bowler Hat",
            "Price": 34.45,
            "Quantity": 2
          },
          {
            "Product Name": "Trilby hat",
            "Price": 21.67,
            "Quantity": 1
          }
        ]
      },
      {
        "OrderID": "order104",
        "Product": [
          {
            "Product Name": "Bowler Hat",
            "Price": 34.45,
            "Quantity": 4
          },
          {
            "Product Name": "Cloak",
            "Price": 107.99,
            "Quantity": 1
          }
        ]
      }
    ]
  }
}

We want to calculate the total value of all products in all orders. The following transform performs this calculation.

$sum(event.value.Account.Order.Product.(Price * Quantity))

The output is the following.

{ "value": 336.36 }

Field-Mapping Example

In this example, assume the event payload contains the following JSON.

{
  "FirstName": "Fred",
  "Surname": "Smith",
  "Age": 28,
  "Address": {
    "Street": "Hursley Park",
    "City": "Winchester",
    "Postcode": "SO21 2JN"
  },
  "Phone": [
    {
      "type": "home",
      "number": "0203 544 1234"
    },
    {
      "type": "office",
      "number": "01962 001234"
    },
    {
      "type": "mobile",
      "number": "077 7700 1234"
    }
  ],
  "Other": {
    "Over 18 ?": true,
    "Misc": null,
    "Alternative.Address": {
      "Street": "Brick Lane",
      "City": "London",
      "Postcode": "E1 6RF"
    }
  }
}

We want to perform some mapping to output the person’s full name (First and Last Name) along with their mobile phone number. The following transform does this.

{
    "name": event.value.FirstName & " " & event.value.Surname,
    "mobile": event.value.Phone[type = "mobile"].number
}

The output is the following.

{
    "value": {
        "name": "Fred Smith",
        "mobile": "077 7700 1234"
    }
}