Nested JSON to rows

Recipe: flatten nested JSON exports from a help desk or chat platform into one row per message, joined to conversation-level fields.

When you need it

Your data comes from an API or a data export as JSON: a folder of files, each an array of messages, with nested objects (sender.id, source.type), HTML bodies, and a separate file of conversation-level fields such as status, satisfaction score and tags. Help desks and chatbot platforms commonly export this way.

Input

Example input: a folder export/ with two message files:

[
  { "id": "m1", "ticket_id": 7001, "channel": "email", "from_agent": false,
    "sender": { "id": "cus_51", "email": "[email protected]" },
    "body_html": "<p>Hi,</p><p>The grinder I bought makes a &quot;clicking&quot; noise.</p>",
    "created_datetime": "2026-03-04T09:15:00+00:00", "source": { "type": "email" } },
  { "id": "m2", "ticket_id": 7001, "channel": "email", "from_agent": true,
    "sender": { "id": "agt_3", "email": "[email protected]" },
    "body_html": "Customer is a subscriber, offer exchange.",
    "created_datetime": "2026-03-04T09:20:00+00:00", "source": { "type": "internal-note" } },
  { "id": "m3", "ticket_id": 7001, "channel": "email", "from_agent": true,
    "sender": { "id": "agt_3", "email": "[email protected]" },
    "body_html": "<div>Sorry to hear that!<br>We'll send a replacement today.</div>",
    "created_datetime": "2026-03-04T09:31:00+00:00", "source": { "type": "email" } }
]

[
  { "id": "m4", "ticket_id": 7002, "channel": "chat", "from_agent": false,
    "sender": { "id": "cus_77", "email": "[email protected]" },
    "body_html": "Do you ship to Canada?", "created_datetime": "2026-03-05T16:00:00+00:00", "source": { "type": "chat" } },
  { "id": "m5", "ticket_id": 7002, "channel": "chat", "from_agent": true,
    "sender": { "id": "agt_8", "email": "[email protected]" },
    "body_html": "", "created_datetime": "2026-03-05T16:01:00+00:00", "source": { "type": "chat" } }
]

And one file of ticket-level fields:

{ "data": [
  { "id": 7001, "status": "closed", "satisfaction": { "score": 4 }, "tags": ["grinder", "replacement"] },
  { "id": 7002, "status": "open", "satisfaction": null, "tags": ["shipping"] }
] }

Output

sessionId,userId,incoming,text,dimensionlabs_timestamp,conversationId,channel,message_id,status,csat,tags
7001,cus_51,true,"Hi, The grinder I bought makes a ""clicking"" noise.",1772615700000,7001,email,m1,closed,4,"grinder, replacement"
7001,cus_51,false,Sorry to hear that! We'll send a replacement today.,1772616660000,7001,email,m3,closed,4,"grinder, replacement"
7002,cus_77,true,Do you ship to Canada?,1772726400000,7002,chat,m4,open,,shipping

Rules

Walk down to the message. Each output row is one message. Pick nested fields by path: m.sender.id, m.source?.type. Optional chaining (?.) keeps a missing object from crashing the run.
Skip what the customer never saw. Internal notes (source.type is internal-note here) are your team talking to itself. Leave them out, or upload them as a separate source if you want to analyze them.
Convert HTML to text. Turn <br>, </p> and </div> into spaces, drop the remaining tags, and decode entities such as ". See Clean ticket and email text for quoted replies and signatures.
Drop empty messages (attachments only, system events) instead of writing placeholder text.
The ticket is the session. Use the ticket or conversation id as sessionId, not the customer’s email: keyed by email, every ticket a customer ever opened would become one long session, and an email address is personal data that should never be an id.
Use the customer’s id on every row. userId is the customer’s sender.id on their messages and on the agent’s replies. Never use sender.email.
Join conversation fields by id. Status, satisfaction score and tags come from the second file and are repeated on every message of the ticket. Turn arrays into a readable list (grinder, replacement).
Keep the message id. A message_id column lets you find duplicates later and check what arrived.
Read files in a fixed order (sorted by name), so reruns give the same output.

Script

Node 18 or later, with npm install papaparse.

// Flatten a folder of nested JSON message exports into rows, joined to ticket-level fields.
// Usage: node json-to-rows.mjs export/ tickets.json messages.csv
import fs from 'node:fs';
import path from 'node:path';
import Papa from 'papaparse';

const [folder, ticketsFile, output] = process.argv.slice(2);

// Ticket-level fields, keyed by ticket id, joined onto every message of that ticket.
const tickets = new Map(JSON.parse(fs.readFileSync(ticketsFile, 'utf8')).data.map((t) => [String(t.id), {
  status: t.status,
  csat: t.satisfaction?.score ?? '',
  tags: (t.tags || []).join(', '),
}]));

const htmlToText = (html) => (html || '')
  .replace(/<br\s*\/?>|<\/p>|<\/div>/gi, ' ')
  .replace(/<[^>]+>/g, '')
  .replace(/&quot;/g, '"').replace(/&#39;/g, "'").replace(/&lt;/g, '<').replace(/&gt;/g, '>').replace(/&nbsp;/g, ' ').replace(/&amp;/g, '&')
  .replace(/\s+/g, ' ').trim();

const files = fs.readdirSync(folder).filter((f) => f.endsWith('.json')).sort();
const rows = [];
const skipped = { internalNote: 0, emptyText: 0 };
for (const file of files) {
  for (const m of JSON.parse(fs.readFileSync(path.join(folder, file), 'utf8'))) {
    if (m.source?.type === 'internal-note') { skipped.internalNote++; continue; } // never shown to the customer
    const text = htmlToText(m.body_html);
    if (!text) { skipped.emptyText++; continue; }
    const ticketId = String(m.ticket_id);
    rows.push({
      sessionId: ticketId,             // the ticket, not the customer's email: one session per ticket
      userId: m.from_agent ? '' : m.sender.id, // replies get the customer's id below
      incoming: m.from_agent ? 'false' : 'true',
      text,
      dimensionlabs_timestamp: Date.parse(m.created_datetime),
      conversationId: ticketId,
      channel: m.channel,
      message_id: m.id,
      ...(tickets.get(ticketId) || {}),
    });
  }
}

// The agent's replies carry the customer's id: the first customer sender in the ticket, or the ticket id.
const customerOf = new Map();
for (const r of rows) if (r.userId && !customerOf.has(r.sessionId)) customerOf.set(r.sessionId, r.userId);
for (const r of rows) if (!r.userId) r.userId = customerOf.get(r.sessionId) || r.sessionId;

const fields = ['sessionId', 'userId', 'incoming', 'text', 'dimensionlabs_timestamp', 'conversationId', 'channel', 'message_id', 'status', 'csat', 'tags'];
fs.writeFileSync(output, Papa.unparse({ fields, data: rows.map((r) => fields.map((f) => r[f] ?? '')) }, { newline: '\n' }) + '\n');
const n = (count, word) => `${count} ${word}${count === 1 ? '' : 's'}`;
console.log(`${files.length} files -> ${rows.length} rows; skipped ${n(skipped.internalNote, 'internal note')}, ${n(skipped.emptyText, 'empty message')}`);

node json-to-rows.mjs export/ tickets.json messages.csv
# 2 files -> 3 rows; skipped 1 internal note, 1 empty message

Variations

One message per line (JSONL): the toolkit reads JSONL directly and takes dot paths in its mapping, for example "userId": "sender.id".
Messages nested inside conversations (conversations[].messages[]): loop over conversations, then over their messages, and take the conversation fields from the outer object.
A file that isn’t valid JSON because two arrays were written back to back (][) or objects without commas between them (}{): ask the source system for a clean export first. If you must repair it, replace ][ with ,; for objects written back to back, replace }{ with },{ and wrap the whole file in [ and ]. Either string can also occur inside message text, so check the record count against the source afterwards.
Sending to the API instead of uploading: build the same objects and POST each one with sendToDimensionLabs from the universal API, with the extra fields in platformJson.