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

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

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

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

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

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. For regex replacement use remove/replaceAll (Strings).