🗺️ The Ultimate AI Automation Roadmap — Part 4 of 20 | Tier: Foundations | Difficulty: Beginner–Intermediate | Est. read: 15 min

Part 4: JSON & Data Transformation Mastery for n8n — Merge, Flatten & Reshape API Data

Search Intent: API data is rarely in the format you need. You get deeply nested objects, inconsistent field names, arrays inside arrays, and missing values. This is the #1 frustration for new automation engineers. This guide teaches every n8n transformation technique: the Set node for field mapping, the Code node for custom JavaScript, the Merge node for joining data from multiple sources, and the Aggregate node for rollups — with a complete legal document processing pipeline as the real-world example.

📋 Table of Components

n8n Node	Primary Purpose	Best For
Set Node	Create, rename, delete fields	Field name mapping between APIs
Code Node (JS)	Custom JavaScript logic	Complex transforms, calculations
Merge Node	Join data from 2 workflow branches	Enriching data from multiple sources
Split In Batches	Process arrays item by item	Bulk operations on lists
Aggregate Node	Group and summarize	Totals, averages, group-by
Item Lists Node	Array operations	Sort, deduplicate, limit arrays

🌍 Real-World Scenario: Legal Document Processing Pipeline

A law firm automatically processes completed DocuSign contracts: the signed contract arrives with DocuSign’s nested XML-to-JSON structure, the client record lives in Clio CRM with different field names, and billing rates come from a Google Sheet. Three data sources, three different formats — merge them into one clean invoice object and post to QuickBooks automatically on every contract completion.

⚙️ Step 1: The Set Node — Field Mapping Made Simple

DocuSign returns signer name as envelope.recipients.signers[0].name but QuickBooks needs it as customer_name. The Set node bridges this gap:

// Set Node configuration (Map mode):
{
  "client_name": "={{ $json.envelope.recipients.signers[0].name }}",
  "client_email": "={{ $json.envelope.recipients.signers[0].email }}",
  "contract_date": "={{ $json.envelope.completedDateTime.split('T')[0] }}",
  "contract_value": "={{ Number($json.envelope.customFields.textCustomFields[0].value) }}",
  "envelope_id": "={{ $json.envelopeId }}"
}
// Enable "Keep Only Set" to discard all other fields

⚙️ Step 2: The Merge Node — Join Data from Multiple Sources

After fetching the DocuSign data (Branch 1) and the Clio CRM client record (Branch 2), use the Merge node to combine them: Mode: Merge by Key. Input 1 Key: client_email. Input 2 Key: client.primary_email_address. n8n finds matching records from both branches and merges all fields into one item per matched pair. Result: one combined object with all DocuSign + Clio fields available for the next node.

⚙️ Step 3: The Code Node — Build the Invoice Object

// Code Node: Build invoice from merged data + Google Sheets billing rate
const merged = $input.first().json;
const rate = $('Billing Rate Sheet').first().json; // from parallel branch

const hours = parseFloat(merged.contract_hours) || 1;
const hourlyRate = parseFloat(rate.standard_rate) || 350;
const subtotal = hours * hourlyRate;

return [{ json: {
  invoice_number: 'INV-' + Date.now(),
  customer_name: merged.client.display_name,
  customer_email: merged.client_email,
  matter_ref: merged.matter_number || 'GENERAL',
  description: 'Legal Services - ' + merged.contract_type,
  hours_billed: hours,
  rate: hourlyRate,
  subtotal: subtotal,
  tax: subtotal * 0.1,
  total: subtotal * 1.1,
  due_date: new Date(Date.now() + 30*24*60*60*1000).toISOString().split('T')[0],
  currency: 'USD',
  docusign_envelope_id: merged.envelope_id
}}];

⚙️ Step 4: Split In Batches — Bulk Processing

When processing a list of contracts at once (e.g., end-of-month batch), use Split In Batches before the Set node. Set Batch Size to 1 for sequential processing. Add a Wait node (300ms) between batches to respect QuickBooks API rate limits (100 requests/minute). After Split In Batches finishes all items, data flows to a final Aggregate node for a summary report.

🔁 Automation Logic: Legal Invoice Generation

Stage	What Happens	Node
🟢 Trigger	DocuSign webhook — envelope status = completed	Webhook Node
⚡ Branch 1	GET full envelope data from DocuSign API	HTTP Request → Set Node
⚡ Branch 2	GET matching client from Clio by email	HTTP Request (Clio API)
⚡ Branch 3	GET billing rate from Google Sheets	Google Sheets Node
🔍 Merge	Combine all 3 sources by client_email	Merge Node (by key)
⚡ Transform	Build invoice object with Code node	Code Node (JavaScript)
📤 Output 1	POST invoice to QuickBooks Online	HTTP Request (POST)
📤 Output 2	PATCH Clio matter status to Billed	HTTP Request (PATCH)
📤 Output 3	Log to Google Sheets audit trail	Google Sheets (Append Row)

🚀 Next: Part 5

Part 5 connects OpenAI GPT-4o to n8n and builds your first AI-powered intelligent workflow — email classification, entity extraction, and smart routing. This is where automation becomes truly intelligent and the real value multiplier kicks in.