# 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: ```json title="export/messages-001.json" [ { "id": "m1", "ticket_id": 7001, "channel": "email", "from_agent": false, "sender": { "id": "cus_51", "email": "ana@example.com" }, "body_html": "

Hi,

The grinder I bought makes a "clicking" noise.

", "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": "sam@lumen.example" }, "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": "sam@lumen.example" }, "body_html": "
Sorry to hear that!
We'll send a replacement today.
", "created_datetime": "2026-03-04T09:31:00+00:00", "source": { "type": "email" } } ] ``` ```json title="export/messages-002.json" [ { "id": "m4", "ticket_id": 7002, "channel": "chat", "from_agent": false, "sender": { "id": "cus_77", "email": "bo@example.com" }, "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": "jo@lumen.example" }, "body_html": "", "created_datetime": "2026-03-05T16:01:00+00:00", "source": { "type": "chat" } } ] ``` And one file of ticket-level fields: ```json title="tickets.json" { "data": [ { "id": 7001, "status": "closed", "satisfaction": { "score": 4 }, "tags": ["grinder", "replacement"] }, { "id": 7002, "status": "open", "satisfaction": null, "tags": ["shipping"] } ] } ``` ## Output ```csv title="messages.csv" 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 `
`, `

` and `` into spaces, drop the remaining tags, and decode entities such as `"`. See [Clean ticket and email text](/transform/recipe-clean-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`. ```js title="json-to-rows.mjs" // 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 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(/|<\/p>|<\/div>/gi, ' ') .replace(/<[^>]+>/g, '') .replace(/"/g, '"').replace(/'/g, "'").replace(/</g, '<').replace(/>/g, '>').replace(/ /g, ' ').replace(/&/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')}`); ``` ```bash 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](/transform/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](/api/tracker#the-same-thing-in-code), with the extra fields in `platformJson`.