Transform by JSONata node

The Transform by JSONata node enables advanced JSON data transformation and querying using JSONata expressions. It allows you to restructure, extract, filter, and format data from complex JSON objects or arrays, making it ideal for preparing data for external APIs or internal processing.

When to use

Simplify complex objects from SAP or NetSuite into flat structures
Extract specific fields from nested API responses
Format dates from ISO format to custom formats
Combine multiple fields into computed values
Filter and reshape arrays of records
Map fields between different data structures

To learn about JSONata expressions, refer to jsonata.org.

Input parameters

Input

object

required

The JSON object or array to transform. Can reference variables from previous nodes or DataSlots, or contain static JSON. Reference its top-level keys directly in the Query.

Query

object

A JSONata expression that transforms or queries the input data. Reference the input’s top-level fields directly (for example, Account.Order). The result of the expression becomes the node’s output.

Type

dropdown

Optional output field type used to format the result. Options: Date/Time (requires a Format for the output date) and Phone.

Input Example:

{
  "Account": {
    "Account Name": "Firefly",
    "Order": [
      { "OrderID": "order103", "Product": [ { "Product Name": "Bowler Hat", "Price": 34.45, "Quantity": 2 } ] }
    ]
  }
}

Query Example:

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

Output

The Transform node outputs the result of the JSONata expression applied to the input data. The result is returned directly — there is no wrapper key.

{
  "vendor_name": "Acme Corporation",
  "vendor_id": "V001234",
  "formatted_date": "2024-01-15"
}

Adding to your workflow

Add the node

Click Add Node in the workflow canvas, select Transform from the list of available nodes, then position it in your workflow.

Configure input data

Provide the JSON object or array to transform. Reference data from previous nodes using variable syntax or provide static JSON.

Write JSONata query

Create your transformation expression using JSONata syntax, referencing the input’s top-level fields directly. The result of the expression becomes the node output.

Test the node

Run the node to verify the JSONata expression produces the expected output structure.

JSONata reference

Data Access

Syntax	Description	Example
Field Access	Access object properties	`vendor.name`, `address.city`
Array Access	Access array elements	`items[0]`, `records[-1]`
Conditional Access	Default values for missing fields	`vendor.phone ? vendor.phone : "N/A"`
Wildcard	Access all elements	`.name`, `items..id`

String Functions

Function	Description	Example
`$uppercase()`	Convert to uppercase	`$uppercase(vendor.name)`
`$lowercase()`	Convert to lowercase	`$lowercase(status)`
`$substring()`	Extract substring	`$substring(id, 0, 5)`
`$trim()`	Remove whitespace	`$trim(description)`
`$replace()`	Replace text	`$replace(phone, "-", "")`
`&`	Concatenate strings	`FirstName & " " & LastName`

Date Functions

Function	Description	Example
`$now()`	Current timestamp	`$now()`
`$toMillis()`	Convert to milliseconds	`$toMillis(created_at)`
`$fromMillis()`	Format from milliseconds	`$fromMillis($toMillis(date), "[Y0001]-[M01]-[D01]")`

Array Functions

Function	Description	Example
`$map()`	Transform each element	`$map(items, function($v){ $v.name })`
`$filter()`	Filter elements	`$filter(items, function($v){ $v.status = "active" })`
`$reduce()`	Reduce to single value	`$reduce(items, function($acc, $v){ $acc + $v.amount }, 0)`
`$count()`	Count elements	`$count(records)`
`$sum()`	Sum numeric values	`$sum(items.price)`
`$average()`	Calculate average	`$average(scores)`

Operators

Operator	Description	Example
`+`, `-`, `*`, `/`	Arithmetic	`price * quantity`
`=`, `!=`	Equality	`status = "active"`
`>`, `<`, `>=`, `<=`	Comparison	`amount > 1000`
`and`, `or`	Logical	`status = "active" and amount > 100`
`?`	Conditional	`score > 80 ? "high" : "low"`

Examples

Flatten SAP Vendor Response

Transform nested SAP vendor data into a flat structure for internal processing.Input:

    {
      "input": "{{sap_vendor_response}}"
    }

Sample Input Data:

    {
      "vendor": {
        "LIFNR": "V001234",
        "NAME1": "Acme Corporation",
        "LAND1": "US",
        "ORT01": "Chicago",
        "PSTLZ": "60601"
      },
      "metadata": {
        "ERDAT": "2024-01-15T10:30:00Z",
        "AEDAT": "2024-01-20T14:45:00Z"
      }
    }

Query:

    {
      "vendor_id": input.vendor.LIFNR,
      "name": input.vendor.NAME1,
      "location": input.vendor.ORT01 & ", " & input.vendor.LAND1,
      "created_date": $fromMillis($toMillis(input.metadata.ERDAT), "[Y0001]-[M01]-[D01]")
    }

Output:

    {
      "vendor_id": "V001234",
      "name": "Acme Corporation",
      "location": "Chicago, US",
      "created_date": "2024-01-15"
    }

Map NetSuite Records to Name-Value Pairs

Convert NetSuite records into name-value pairs for Config DataSource dropdowns.Input:

    {
      "records": "{{netsuite_vendors.records}}"
    }

Query:

    $map(records, function($v){
      {
        "name": $v.companyName,
        "value": $v.internalId
      }
    })

Output:

    [
      { "name": "Acme Corporation", "value": "12345" },
      { "name": "TechSupply Inc", "value": "12346" },
      { "name": "Global Parts Ltd", "value": "12347" }
    ]

Filter and Transform Order Array

Filter active orders and extract specific fields with formatted dates.Input:

    {
      "orders": "{{netsuite_orders.records}}"
    }

Sample Input Data:

    [
      { "id": "SO-001", "customer": "Acme Corp", "status": "active", "total": 5000, "date": "2024-01-15T10:00:00Z" },
      { "id": "SO-002", "customer": "TechSupply", "status": "closed", "total": 3000, "date": "2024-01-10T09:00:00Z" },
      { "id": "SO-003", "customer": "Global Ltd", "status": "active", "total": 7500, "date": "2024-01-20T11:00:00Z" }
    ]

Query:

    $map($filter(orders, function($o){ $o.status = "active" }), function($order){
      {
        "order_id": $order.id,
        "customer": $order.customer,
        "amount": $order.total,
        "order_date": $fromMillis($toMillis($order.date), "[Y0001]-[M01]-[D01]")
      }
    })

Output:

    [
      { "order_id": "SO-001", "customer": "Acme Corp", "amount": 5000, "order_date": "2024-01-15" },
      { "order_id": "SO-003", "customer": "Global Ltd", "amount": 7500, "order_date": "2024-01-20" }
    ]

Create Nested Structure from Flat Data

Restructure flat Tipalti payment data into a nested format for reporting.Input:

    {
      "payment": "{{tipalti_payment}}"
    }

Sample Input Data:

    {
      "payee_id": "P001",
      "payee_name": "Vendor ABC",
      "payee_email": "ap@vendorabc.com",
      "amount": 15000,
      "currency": "USD",
      "status": "completed",
      "submitted_date": "2024-01-15T10:00:00Z",
      "completed_date": "2024-01-17T14:30:00Z"
    }

Query:

    {
      "payee": {
        "id": payment.payee_id,
        "name": payment.payee_name,
        "email": payment.payee_email
      },
      "transaction": {
        "amount": payment.amount,
        "currency": payment.currency,
        "status": payment.status
      },
      "dates": {
        "submitted": $fromMillis($toMillis(payment.submitted_date), "[Y0001]-[M01]-[D01]"),
        "completed": $fromMillis($toMillis(payment.completed_date), "[Y0001]-[M01]-[D01]")
      }
    }

Output:

    {
      "payee": {
        "id": "P001",
        "name": "Vendor ABC",
        "email": "ap@vendorabc.com"
      },
      "transaction": {
        "amount": 15000,
        "currency": "USD",
        "status": "completed"
      },
      "dates": {
        "submitted": "2024-01-15",
        "completed": "2024-01-17"
      }
    }

Calculate Aggregates from Array

Calculate summary statistics from an array of transaction records.Input:

    {
      "transactions": "{{netsuite_transactions.records}}"
    }

Query:

    {
      "total_count": $count(transactions),
      "total_amount": $sum(transactions.amount),
      "average_amount": $round($average(transactions.amount), 2),
      "max_amount": $max(transactions.amount),
      "min_amount": $min(transactions.amount)
    }

Output:

    {
      "total_count": 25,
      "total_amount": 125000,
      "average_amount": 5000,
      "max_amount": 15000,
      "min_amount": 500
    }

Troubleshooting

Empty or Null Output

Problem: Transform node returns empty response

Cause	Solution
Empty Query	Provide a JSONata expression in the Query field.
Field not found	Reference the input’s top-level keys exactly as they appear in the Input JSON (e.g. `Account.Order`).
Input field missing	Verify the Input field contains valid JSON data.

JSONata Syntax Errors

Problem: Node returns syntax error instead of transformed data

Cause	Solution
Missing quotes or brackets	Check for balanced quotes and brackets
Invalid function name	Verify function names are correct (case-sensitive)
Wrong operator	Use `=` for equality, `&` for concatenation
Test in playground	Use jsonata.org to validate syntax

Missing Field References

Problem: Query returns null for expected values

Cause	Solution
Field name mismatch	Field names are case-sensitive, verify exact names
Wrong path	Use the full path from the input’s top-level key (e.g. `vendor.name` if the input has a `vendor` object)
Field does not exist	Add conditional: `field ? field : "default"`
Use Debug Node	Inspect actual input structure before Transform

Date Formatting Issues

Problem: Date functions produce unexpected results

Cause	Solution
Invalid date format	Ensure input dates are valid ISO format
Missing toMillis	Always convert to milliseconds first: `$toMillis(date)`
Wrong format string	Use `[Y0001]-[M01]-[D01]` for YYYY-MM-DD

Array Processing Errors

Problem: Array functions fail or return unexpected results

Cause	Solution
Input not an array	Verify input is actually an array with `$type()`
Function syntax error	Check function signature matches JSONata spec
Empty array	Add check: `$count(array) > 0 ? $map(...) : []`

Use the JSONata online playground to test and debug your expressions before adding them to the Transform node.

Next steps

Custom Code node

Process complex data with JavaScript or Python.

Data Mapper node

Apply simple field name transformations.

Rule node

Add conditional transformation logic.

Loop node

Apply transformations across arrays.

​When to use

​Input parameters

​Output

​Adding to your workflow

​JSONata reference

​Examples

​Troubleshooting

​Next steps

Custom Code node

Data Mapper node

Rule node

Loop node

When to use

Input parameters

Output

Adding to your workflow

JSONata reference

Examples

Troubleshooting

Next steps