json to csv
JSON to CSV with Nested Arrays — Complete Guide
Why JSON-to-CSV is harder than it looks
Flat JSON with string and number values maps directly to CSV columns. The challenge starts with nested objects and arrays. CSV is a strictly two-dimensional format — rows and columns — while JSON is a tree. Bridging that gap requires a flattening strategy, and the strategy you choose depends on what the downstream consumer expects.
A spreadsheet user wants readable column names like user.address.city. A data pipeline might want array items exploded into separate rows. A reporting tool might want array elements concatenated into a single cell. There is no universal right answer — this guide covers all three approaches.
The simple case: flat JSON arrays
When your JSON is an array of flat objects (no nesting), conversion is straightforward: collect all unique keys as headers and map each object to a row.
function flatJsonToCsv(records) {
if (!records.length) return '';
const headers = [...new Set(records.flatMap(Object.keys))];
const escapeCsv = (val) => {
const s = val == null ? '' : String(val);
return s.includes(',') || s.includes('"') || s.includes('\n')
? '"' + s.replace(/"/g, '""') + '"'
: s;
};
const rows = records.map(row =>
headers.map(h => escapeCsv(row[h])).join(',')
);
return [headers.map(escapeCsv).join(','), ...rows].join('\n');
}
// Example
const data = [
{ id: 1, name: 'Alice', email: 'alice@example.com' },
{ id: 2, name: 'Bob', email: 'bob@example.com' },
];
console.log(flatJsonToCsv(data));
// id,name,email
// 1,Alice,alice@example.com
// 2,Bob,bob@example.comThe escapeCsv function handles the three cases that require quoting in RFC 4180 CSV: values containing commas, values containing double quotes (doubled for escaping), and values containing newline characters.
Flattening nested objects with dot notation
When a JSON object contains nested objects, the most common approach is dot notation: { user: { name: 'Alice', city: 'NYC' } } becomes two columns named user.name and user.city. This preserves the complete data and produces column names that are self-documenting.
function flattenObject(obj, prefix = '', result = {}) {
for (const [key, value] of Object.entries(obj ?? {})) {
const fullKey = prefix ? `${prefix}.${key}` : key;
if (value !== null && typeof value === 'object' && !Array.isArray(value)) {
// Recurse into nested objects
flattenObject(value, fullKey, result);
} else {
result[fullKey] = value ?? '';
}
}
return result;
}
// Example
flattenObject({ id: 1, user: { name: 'Alice', address: { city: 'NYC' } } });
// → { id: 1, 'user.name': 'Alice', 'user.address.city': 'NYC' }This handles arbitrary nesting depth. The recursion stops at primitives and at null values. Arrays are left as-is at this stage — the next section covers array handling.
Handling arrays of primitives
Arrays of primitive values (strings, numbers) are best represented as a single cell with a delimiter. Semicolon is the conventional choice because it does not conflict with CSV's own comma separator.
function flattenObject(obj, prefix = '', result = {}) {
for (const [key, value] of Object.entries(obj ?? {})) {
const fullKey = prefix ? `${prefix}.${key}` : key;
if (Array.isArray(value)) {
if (value.length === 0) {
result[fullKey] = '';
} else if (value.every(v => typeof v !== 'object' || v === null)) {
// Array of primitives → join with semicolon
result[fullKey] = value.join(';');
} else {
// Array of objects → flatten each with an index suffix
value.forEach((item, i) => {
if (typeof item === 'object' && item !== null) {
flattenObject(item, `${fullKey}[${i}]`, result);
} else {
result[`${fullKey}[${i}]`] = item ?? '';
}
});
}
} else if (value !== null && typeof value === 'object') {
flattenObject(value, fullKey, result);
} else {
result[fullKey] = value ?? '';
}
}
return result;
}
// Example
flattenObject({ tags: ['json', 'api', 'debug'], scores: [95, 87] });
// → { tags: 'json;api;debug', scores: '95;87' }
flattenObject({ items: [{ id: 1, name: 'a' }, { id: 2, name: 'b' }] });
// → { 'items[0].id': 1, 'items[0].name': 'a', 'items[1].id': 2, 'items[1].name': 'b' }The index-suffix approach for arrays of objects produces the most complete CSV but also the most columns. If all records have the same array length, the output is clean. If lengths differ, some cells will be empty — which is correct and expected.
Building the complete converter
Combining the flatten function with the CSV formatter produces a complete JSON-to-CSV converter that handles nested objects, arrays of primitives, and arrays of objects:
function jsonToCsv(input) {
// Accept either a JSON string or a parsed array
const records = typeof input === 'string' ? JSON.parse(input) : input;
if (!Array.isArray(records)) {
throw new Error('Input must be a JSON array of objects');
}
if (records.length === 0) return '';
// Flatten every record
const flat = records.map(r => flattenObject(r));
// Collect all unique column headers, preserving insertion order
const headers = [...new Set(flat.flatMap(Object.keys))];
const escapeCsv = (val) => {
const s = val == null ? '' : String(val);
return s.includes(',') || s.includes('"') || s.includes('\n')
? '"' + s.replace(/"/g, '""') + '"'
: s;
};
const rows = flat.map(row =>
headers.map(h => escapeCsv(row[h] ?? '')).join(',')
);
return [headers.join(','), ...rows].join('\n');
}Edge cases and gotchas
Several edge cases can trip up a naive implementation. Null values should produce empty CSV cells, not the string 'null'. Boolean values should produce 'true' and 'false' strings. Numbers should be preserved as-is without extra quoting.
- null values → empty string, not 'null'
- undefined values → empty string
- Boolean false → the string 'false' (not empty, which would be falsy-confusing)
- Numbers containing commas (European locale) → wrap in quotes
- Strings that look like numbers → preserve as strings to avoid data loss
- Empty arrays [] → empty cell
- Nested objects mixed with primitives at the same key → flatten consistently
- Unicode characters and emoji → CSV handles UTF-8; ensure the file is saved with UTF-8 BOM if opening in Excel
Excel and Google Sheets both expect UTF-8 CSV files. If your data contains non-ASCII characters, prefix the output with the UTF-8 BOM (\uFEFF) to prevent character encoding issues when opening the file on Windows.
Using Just Formatter for instant conversion
Writing a custom converter makes sense when you need batch processing, a specific column structure, or integration with a pipeline. For one-off conversions during development or data exploration, Just Formatter's JSON to CSV converter handles the flattening automatically — paste your JSON array, download the CSV, and open it in a spreadsheet in seconds.
The tool handles nested objects with dot notation and arrays of primitives with semicolon joining. For deeply nested structures with array-of-objects patterns, it uses index notation to ensure no data is lost in the output.