Skip to main content

TransformScript Cookbook

Task-oriented recipes for common TransformScript transforms. Each recipe is a complete, ready-to-use script that shows its input and output. Paste one in, swap in your own field names, and adapt it to your needs.

The input directive is optional: TransformScript auto-detects the format from the file's extension (see Input & Output Formats). The recipes keep input explicit for clarity, but in an Automation you can usually drop it.

Reshaping and Mapping

A receiving system may expect different field names, nesting, or codes from those in the source file. Build the output from the fields it needs, filtering records and mapping values as part of that transformation.

Rename and Reshape Fields

Pull a flat output shape out of a nested payload.

%files 1.0
input json
output json
---
{ name: payload.customer.first ++ " " ++ payload.customer.last, total: payload.order_total }

{"customer":{"first":"Ada","last":"Lovelace"},"order_total":120} → {"name":"Ada Lovelace","total":120}

Filter, Then Transform

Keep the rows you want, then map them. Wrap the filter in parentheses — a trailing bare lambda otherwise swallows the map that follows it (see Tips).

%files 1.0
input json
output json
---
(payload.items filter (i) -> i.qty > 0) map (i) -> i.sku

{"items":[{"sku":"a","qty":0},{"sku":"b","qty":5},{"sku":"c","qty":2}]} → ["b","c"]

Include Fields Conditionally

A trailing if on an object entry drops the entry when the condition is falsy, which is useful for omitting nulls and adding flags.

%files 1.0
input json
output json
---
{
  name: payload.name,
  nickname: payload.nickname if payload.nickname != null,
  tier: "gold" if payload.vip
}

{"name":"Pat","nickname":null,"vip":true} → {"name":"Pat","tier":"gold"}

Map Codes to Labels (Lookup Table)

Use a var object as a lookup. Index an object with a dynamic key using [(expr)] (or .(expr)); plain [expr] is array indexing only. default supplies the fallback.

%files 1.0
input json
output json
var labels = { A: "Active", B: "Blocked" }
---
payload map (code) -> labels[(code)] default "Unknown"

["A","B","Z"] → ["Active","Blocked","Unknown"]

Format Conversion

Select the output format required by the receiving system, then return a data shape that can be written in that format. For CSV output, uniform row objects let the writer derive a consistent header.

CSV to JSON

With a CSV input, payload is an array of row objects keyed by header. Coerce the columns you need.

%files 1.0
input csv
output json
---
payload map (r) -> { name: r.name, age: r.age as Number }

name,age / Alice,30 / Bob,40 → [{"name":"Alice","age":30},{"name":"Bob","age":40}]

JSON to CSV (With Header Row)

Return an array of uniform objects; the keys become the header.

%files 1.0
input json
output csv header=true
---
payload.users map (u) -> { UserID: u.id, Email: u.email }

{"users":[{"id":1,"email":"a@x.com"},{"id":2,"email":"b@x.com"}]} →

UserID,Email
1,a@x.com
2,b@x.com

Normalizing and Enriching

A file can have the right structure while its values still need adjustment. Convert text to the types the receiving system expects and format dates or composed strings before writing the result.

Normalize Strings to Real Types

Partner feeds often send everything as strings. Cast with as.

%files 1.0
input json
output json
---
payload map (r) -> { id: r.id as Number, active: r.active as Boolean, price: r.price as Number }

[{"id":"1","active":"true","price":"9.5"},{"id":"2","active":"false","price":"3"}] → [{"id":1,"active":true,"price":9.5},{"id":2,"active":false,"price":3}]

Reformat a Date

Parse with toDate, render with as String { format: … }. Formats use strftime tokens (%m/%d/%Y), not MM/dd/yyyy.

%files 1.0
input json
output json
---
{ iso: payload.d, us: toDate(payload.d) as String { format: "%m/%d/%Y" } }

{"d":"2024-01-02"} → {"iso":"2024-01-02","us":"01/02/2024"}

Build a String

Interpolate with $( … ) inside any string.

%files 1.0
input json
output json
---
{ greeting: "Hello $(payload.first), you have $(payload.count) messages" }

{"first":"Ada","count":3} → {"greeting":"Hello Ada, you have 3 messages"}

Aggregation and Relationships

Some outputs depend on a collection of records rather than one record at a time. Grouping, totals, joins, and changes to collection structure let you produce a summary or combine related data.

Group and Aggregate

groupBy builds an object keyed by the grouping value; mapObject reduces each group. Parenthesize the groupBy before chaining mapObject.

%files 1.0
input json
output json
---
(payload.sales groupBy (s) -> s.region) mapObject (rows, region) -> { (region): sumBy(rows, (r) -> r.amt) }

{"sales":[{"region":"E","amt":10},{"region":"W","amt":5},{"region":"E","amt":7}]} → {"E":17.0,"W":5.0}

Compute Line Totals and a Grand Total

using introduces an intermediate value so you can reference it twice.

%files 1.0
input json
output json
---
using (priced = payload.lines map (l) -> { qty: l.qty, price: l.price, total: l.qty * l.price })
{ lines: priced, grandTotal: sumBy(priced, (l) -> l.total) }

{"lines":[{"qty":2,"price":5},{"qty":1,"price":8}]} → {"lines":[{"qty":2,"price":5,"total":10},{"qty":1,"price":8,"total":8}],"grandTotal":18.0}

Join Two Datasets

leftJoin(left, right, leftKey, rightKey) returns {l, r} pairs; unmatched left rows come back with l only.

%files 1.0
input json
output json
---
leftJoin(payload.orders, payload.customers, (o) -> o.cust, (c) -> c.code)

{"orders":[{"id":1,"cust":"x"},{"id":2,"cust":"y"}],"customers":[{"code":"x","name":"Xenon"}]} → [{"l":{"id":1,"cust":"x"},"r":{"code":"x","name":"Xenon"}},{"l":{"id":2,"cust":"y"}}]

Deduplicate

%files 1.0
input json
output json
---
payload distinctBy (r) -> r.email

[{"email":"a@x.com"},{"email":"a@x.com"},{"email":"b@x.com"}] → [{"email":"a@x.com"},{"email":"b@x.com"}]

Sort

%files 1.0
input json
output json
---
payload orderBy (r) -> r.p

[{"n":"c","p":3},{"n":"a","p":1},{"n":"b","p":2}] → [{"n":"a","p":1},{"n":"b","p":2},{"n":"c","p":3}]

Flatten Nested Arrays

%files 1.0
input json
output json
---
payload.carts flatMap (c) -> c.items

{"carts":[{"items":["a","b"]},{"items":["c"]}]} → ["a","b","c"]

Pivot an Array of Pairs Into an Object

Fold the array with reduce, merging a one-entry object each step. Seed the accumulator with acc default {}.

%files 1.0
input json
output json
---
payload reduce (e, acc) -> (acc default {}) ++ { (e.k): e.v }

[{"k":"a","v":1},{"k":"b","v":2}] → {"a":1,"b":2}

Logic and Routing

Conditional expressions let the output depend on a value or pattern in the input. Use them to classify records or change a selected part of a nested document while retaining the rest.

Classify With match

%files 1.0
input json
output json
---
payload map (amt) -> amt match {
  case a if a < 100 -> "small"
  case a if a < 1000 -> "medium"
  else -> "large"
}

[5,150,1500] → ["small","medium","large"]

Patch a Nested Value With update

update returns a new structure with the selected path replaced; $ is the existing value at the path.

%files 1.0
input json
output json
---
payload update {
  case role at .user.role -> "admin"
}

{"user":{"name":"sam","role":"basic"}} → {"user":{"name":"sam","role":"admin"}}

Tips and Gotchas

  • Parenthesize UFCS stages with bare lambdas. In a filter (x) -> x.ok map (x) -> x.id, the map is parsed as part of the filter lambda's body. Wrap each stage: (a filter (x) -> x.ok) map (x) -> x.id. (Prefix calls — map(filter(a, …), …) — avoid the issue entirely.)
  • Object keys are dynamic with [(key)] or .(key). Plain obj[key] is array indexing and returns null on an object.
  • Date/time formats are strftime. Use %Y-%m-%d, %m/%d/%Y, etc. Java-style yyyy-MM-dd is treated as literal text.
  • sumBy/avg return decimals. Totals come back as 17.0, not 17.
  • No pipe operator. Chain with UFCS (receiver fn(args)) or thread a whole value with then. See Functions & Lambdas.
  • replace is literal; matches is unanchored. Use remove for regex deletion and replace or replaceAll for literal replacement (Strings).