How to Join Two Files by a Shared Key

Published 2026-09-072 min readCopy link

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):

$ npx github:mahope/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:

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

$ npx github:mahope/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.