Skip to main content

Transform data in flows

Most integrations reshape data between systems: turn a partner's CSV into orders, pick the lines that need a label, total a shipment's weight, or render a file a partner expects. Flows have steps for each of these, and none of them need code. When you do want code, transform.js and the code option of collection.map run it in a sandbox.

StepUse it to
collection.mapTurn every item of a list into a new value
collection.filterKeep the items that match conditions
collection.aggregateCount, sum, average, or find the min and max, overall or per group
collection.sortOrder a list by a field
collection.dedupeRemove repeated items
transform.parseRead CSV, XML, or JSON text into data
transform.serializeWrite data out as CSV, XML, or JSON text
transform.mapApply a field-by-field mapping, saved or written inline

Collection steps take up to 1,000 items and run inside a single step: they never start other runs. Every one of them is billed as one step. items is usually a reference to a list, such as "{{ trigger.payload.lines }}" or "{{ steps.parse.output }}"; a value that is not a list fails the step.

Paths into items​

Collection steps that read a field take a path written without braces: sku, dims.weight, or lines[0].qty. It is read from each item, so sku means item.sku.

Map a list​

collection.map produces one result per item, in order. Give it exactly one of:

  • expression: any value (text, an object, or a list) whose references can read item (the current item) and index (its position, from 0) as well as the usual roots. An object builds one object per item.
  • code: JavaScript run once per item with item, index, and data (the trigger payload). Whatever it returns becomes the result; returning nothing gives null.
{
"key": "lines",
"type": "collection.map",
"config": {
"items": "{{ trigger.payload.order.line_items }}",
"expression": {
"sku": "{{ upper(item.sku) }}",
"qty": "{{ number(item.quantity) }}",
"line": "{{ add(index, 1) }}",
"carrier_code": "{{ lookup('carrier_codes', item.carrier, 'UNKN') }}"
}
}
}

An expression that resolves to nothing for an item gives null, so the output always has one entry per item. Lookups across all the items are loaded together, with one query per lookup table, before any item is mapped.

With code, the whole list shares one sandbox: an instruction budget of about two seconds of work and 32 MB of memory, for all items together. The budget counts work rather than time, so a busy server does not cut a step short, and code runs synchronously within those bounds. A step that uses up either one fails, and retrying will not help.

Each item's result is limited to 64 KB and the whole output to 5 MB (measured as JSON), whether it comes from expression or code. A step that would produce more fails with a message naming the limit, before it builds the rest of the output.

{
"key": "weights",
"type": "collection.map",
"config": {
"items": "{{ trigger.payload.parcels }}",
"code": "return { ref: data.order_id + '-' + (index + 1), oz: Math.ceil(item.weight_lb * 16) }"
}
}

Running each item as its own run (fan-out) is not available yet.

Filter a list​

collection.filter keeps the items whose conditions match, using the same conditions as when guards and branch lanes, with match_mode all (the default) or any. Condition fields can read item and index as well as trigger, steps, inputs, and params.

{
"key": "needs_label",
"type": "collection.filter",
"config": {
"items": "{{ steps.fetch_order.output.line_items }}",
"match_mode": "all",
"conditions": [
{ "field": "item.requires_shipping", "operator": "equals", "value": true },
{ "field": "item.warehouse", "operator": "in_set", "setId": "6c1f9a52-1b6e-4f1e-9a43-2f7d1c0b8e11" }
]
}
}

in_set and not_in_set conditions read each set once for the whole list. A set that belongs to another organization counts as empty.

A regex condition's pattern is limited to 200 characters, and it is only tested against text up to 10 KB; longer text does not match. A pattern can still be slow on text near that size if it backtracks heavily (such as (a+)+$), so keep patterns simple.

Aggregate a list​

collection.aggregate runs operations over the items. Each operation has an op (count, sum, avg, min, or max), a path (required except for count), and the name to store the result under (as).

  • count without a path counts items; with a path it counts items that have a value there.
  • sum, avg, min, and max use values that are numbers or numeric text and ignore the rest. With nothing to work on, sum is 0 and the others are null.

Without group_by the output is one object. With group_by, it is a list with one { "key": ..., ...results } per distinct value, in the order each value first appears; items without a value group under null. The output is limited to 5 MB.

{
"key": "by_warehouse",
"type": "collection.aggregate",
"config": {
"items": "{{ trigger.payload.lines }}",
"group_by": "warehouse",
"operations": [
{ "op": "count", "as": "lines" },
{ "op": "sum", "path": "qty", "as": "units" },
{ "op": "max", "path": "weight_lb", "as": "heaviest_lb" }
]
}
}
[
{ "key": "NJ1", "lines": 2, "units": 5, "heaviest_lb": 12.5 },
{ "key": "CA2", "lines": 1, "units": 1, "heaviest_lb": 3 }
]

Sort a list​

collection.sort orders items by the value at path, direction asc (the default) or desc. Numbers sort as numbers and text by character code (no locale, so Zebra sorts before apple); mixed values sort numbers first, then text, then true and false, then anything else. Items with no value at the path go last in either direction, or first when missing_last is false. The sort is stable: items that compare equal keep their order.

Remove duplicates​

collection.dedupe keeps the first of each repeated item. Without path, whole items are compared by content (the order of an object's keys does not matter). With path, items are compared by the value there, and every item without a value counts as the same.

Parse and serialize documents​

transform.parse reads content text without a mapping. format is csv, xml, or json. Content is limited to 1 MB and 10,000 rows.

  • CSV becomes a list of objects keyed by the header row. Set csv.headers to false to get a list of lists instead, and csv.delimiter for another single-character separator such as ; or a tab. Quoted fields may hold separators, quotes (doubled), and line breaks. A data row with the wrong number of columns fails the step and names the row.
  • XML is read the same way the Exchange mapping engine reads it, so the same paths work in both. Attributes appear with an @_ prefix. Malformed XML fails the step.
  • JSON is parsed as is; invalid JSON fails the step with the parser's message.
{
"key": "parse",
"type": "transform.parse",
"config": {
"content": "{{ trigger.payload.file }}",
"format": "csv",
"csv": { "delimiter": ";", "headers": true }
}
}

transform.serialize writes records as text, with the same limits.

  • CSV needs a list of objects. Columns are csv.columns in that order, or the keys of the first record. A header row is written unless csv.headers is false. Values with commas, quotes, or line breaks are quoted; objects and lists are written as JSON; null is empty.
  • XML needs a list of objects, written as xml.record_element elements (default record) inside xml.root_element (default records). Text is escaped, and keys that are not valid element names have their invalid characters replaced with _.
  • JSON accepts any value and writes it indented.
{
"key": "export",
"type": "transform.serialize",
"config": {
"records": "{{ steps.lines.output }}",
"format": "csv",
"csv": { "columns": ["sku", "qty", "line"] }
}
}

Inline mappings​

transform.map applies an Exchange mapping. Point it at a saved mapping with mapping_id, or write the mapping into the step as definition; exactly one of the two is required. An inline definition has the same shape as a saved mapping version and runs through the same engine, limited to 200 field rules and 64 KB.

FieldTypeDescription
key_fieldstringRequired. The source field that groups rows into one record (the external id)
record_selectorstringJSON and XML: the dot path to the list of records
fieldsarrayRequired, 1 to 200 field rules
constantsobjectValues set on every record before fields are mapped

Each field rule:

FieldTypeDescription
targetstringRequired. The canonical path, such as customer.email or line_items[].sku
sourcestringThe CSV header, JSON or XML path, or a label for a fixed-width column. A rule needs a source or a default
start, lengthintegerFixed width only: the 1-based start column and the width
transformstringtrim, upper, lower, number, money_minor_units, date_iso, or date_format
input_mask, output_maskstringdate_format only; see below
defaultanyUsed when the source is empty
requiredbooleanAn empty source is a row error
patternstringA regular expression the source must match, up to 200 characters inline. Values longer than 10 KB are not checked against it and fail as row errors
enumstring[]The allowed source values
{
"key": "orders",
"type": "transform.map",
"config": {
"direction": "inbound",
"format": "csv",
"content": "{{ trigger.payload.file }}",
"definition": {
"key_field": "PO",
"fields": [
{ "target": "external_id", "source": "PO", "required": true },
{ "target": "ordered_at", "source": "PO Date", "transform": "date_format", "input_mask": "MM/DD/YYYY", "output_mask": "YYYY-MM-DD" },
{ "target": "line_items[].sku", "source": "SKU", "transform": "upper" },
{ "target": "line_items[].quantity", "source": "Qty", "transform": "number" }
]
}
}
}

The definition is checked when you save and publish, with the same rules as a saved mapping.

Dates in mappings​

The date_format transform converts dates between masks, using the same tokens as format_date (YYYY, MM, DD, HH, mm, ss), with the same rules: other letters go in [brackets]. input_mask is how the partner's file writes the date and output_mask is how the canonical record stores it; input_mask must contain YYYY, MM, and DD.

  • Inbound, the file value is read with input_mask and written with output_mask. A value that does not match, or an impossible date, is a row error.
  • Outbound, the masks run the other way: the canonical value is read with output_mask (or as an ISO 8601 date when it does not match) and written with input_mask, so one rule round-trips. A value that cannot be read is an error.

date_format is the only transform that also applies outbound. date_iso keeps working as before.