How to Join Two Files by a Shared Key

Orders in one file, customers in another. Inventory counts in one export, product names in another. Merging them usually means opening Python or loading both into SQLite. Here's the one-command version.

The Transmute CLI (free)

The join operation merges a second row set into your data on a shared key — like a left SQL join. Given cart.json:

[
  { "sku": "A1", "qty": 2 },
  { "sku": "B2", "qty": 1 }
]

and warehouse stock arriving inline (or from a second file via shell substitution):

$ transmute cart.json \
    --pipe '[{"op":"join","on":"sku","keep":"left","prefix":"stock_","with":[{"sku":"A1","warehouse":"EU","stock":42},{"sku":"B2","warehouse":"US","stock":7}]}]' \
    --output json
[
  {
  "sku": "A1",
  "qty": 2,
  "stock_warehouse": "EU",
  "stock_stock": 42
  },
  {
  "sku": "B2",
  "qty": 1,
  "stock_warehouse": "US",
  "stock_stock": 7
  }
]

What the options mean:

Option	Effect
`on`	The shared key. Matching is string-compared, so `"A1"` matches `A1`.
`keep:"left"`	Rows without a match are kept (SQL LEFT JOIN). Default drops them (INNER JOIN).
`prefix`	Joined fields get this prefix so they can't collide with existing column names.
`with`	The rows to merge in. Pass a bigger dataset via shell substitution: `--pipe "[{\"op\":\"join\",\"on\":\"id\",\"with\":$(cat stock.json)}]"`

Chain it like anything else — enrich, then filter and count in one pass:

$ transmute cart.json \
    --pipe '[{"op":"join","on":"sku","keep":"left","with":[...]},{"op":"filter","expr":"item.stock_stock > 0"},{"op":"count"}]' \
    --output json

Compared to the alternatives

SQLite: sqlite3 :memory: '.import cart.csv c' ... is powerful but it's five steps of schema wrangling for what is conceptually one lookup.
Python/pandas: df.merge() is the right tool at scale; for files under a few MB, startup cost exceeds the whole job.
jq: possible with --slurpfile, but the expression syntax for joins is famously hard to get right from memory.

Gotchas

Duplicate keys in the right side: only the last row per key wins — dedupe first if that matters.
Type coercion: numeric-looking keys in CSV become numbers on the left but matching is by string, so joins still line up.