JSON to CSV and Back: Converting Data Formats Without Losing Information

Why JSON and CSV exist for different purposes

JSON and CSV solve different problems. JSON represents arbitrary data structures — nested objects, arrays within arrays, mixed types, optional fields, and hierarchical relationships. CSV represents flat, tabular data — rows of equal-length records, all with the same columns, all as strings. The mismatch between these two models is why JSON-to-CSV conversion requires decisions about structure, and why blind conversion often loses information.

The conversion is most natural when the JSON is already flat — an array of objects where every object has the same scalar fields. It becomes progressively harder as nesting increases, arrays appear inside objects, and field presence varies between records. Understanding where these limits are prevents surprises in production exports.

// Input JSON — flat array of uniform objects
[
  { "id": 1, "name": "Alice", "email": "alice@example.com", "plan": "pro" },
  { "id": 2, "name": "Bob",   "email": "bob@example.com",   "plan": "free" },
  { "id": 3, "name": "Carol", "email": "carol@example.com", "plan": "pro" }
]

// Output CSV
id,name,email,plan
1,Alice,alice@example.com,pro
2,Bob,bob@example.com,free
3,Carol,carol@example.com,pro

// Values that MUST be quoted in CSV
{ "name": "Smith, John" }        // contains a comma
{ "bio": "Line one
Line two" }  // contains a newline
{ "note": 'He said "hello"' }    // contains double quotes

// Correct CSV output
name
"Smith, John"          // comma → wrap in quotes
"Line one
Line two"   // newline → wrap in quotes
"He said ""hello"""    // quotes inside → double them, then wrap

// Wrong (missing quotes)
Smith, John            // parser sees two columns: "Smith" and " John"
He said "hello"        // parser may break depending on implementation

// JavaScript — use a library, not string concatenation
import { stringify } from 'csv-stringify/sync';

const csv = stringify(jsonArray, {
  header: true,
  quoted_string: true   // quote all strings, not just ones that need it
});

// Input with nested object
[
  {
    "id": 1,
    "name": "Alice",
    "address": { "city": "Rome", "country": "IT", "zip": "00100" }
  }
]

// Option 1: Flatten with dot notation (most spreadsheet-friendly)
id,name,address.city,address.country,address.zip
1,Alice,Rome,IT,00100

// Option 2: Serialize as JSON string (preserves all data, less useful in Excel)
id,name,address
1,Alice,"{""city"":""Rome"",""country"":""IT"",""zip"":""00100""}"

// Option 3: Drop nested fields (loses data — avoid unless intentional)
id,name
1,Alice

// JavaScript — flatten with a library
import flatten from 'flat';

const flat = jsonArray.map(row => flatten(row, { delimiter: '.' }));
// flat[0] = { id: 1, name: 'Alice', 'address.city': 'Rome', ... }

// Input with array field
[
  { "id": 1, "name": "Alice", "tags": ["admin", "beta-user"] },
  { "id": 2, "name": "Bob",   "tags": ["beta-user"] }
]

// Option 1: Semicolon-delimited string (simple, readable)
id,name,tags
1,Alice,admin;beta-user
2,Bob,beta-user

// Option 2: Expand to multiple rows (useful for relational analysis)
id,name,tag
1,Alice,admin
1,Alice,beta-user
2,Bob,beta-user

// Option 3: JSON string in cell (preserves all data, poor Excel UX)
id,name,tags
1,Alice,"[""admin"",""beta-user""]"
2,Bob,"[""beta-user""]"

// JavaScript — expand to multiple rows
const expanded = jsonArray.flatMap(row =>
  row.tags.map(tag => ({ id: row.id, name: row.name, tag }))
);

// Input: inconsistent fields
[
  { "id": 1, "name": "Alice", "phone": "+39 06 1234567" },
  { "id": 2, "name": "Bob" },                             // no phone
  { "id": 3, "name": "Carol", "phone": null, "note": "VIP" }  // null + extra field
]

// Correct CSV output (all possible columns, empty cells for missing)
id,name,phone,note
1,Alice,+39 06 1234567,
2,Bob,,
3,Carol,,VIP

// JavaScript — collect all keys first
const allKeys = [...new Set(jsonArray.flatMap(row => Object.keys(row)))];

const rows = jsonArray.map(row =>
  allKeys.map(key => row[key] ?? '')  // ?? '' handles undefined AND null
);

// CSV input
id,name,price,active
1,Widget,9.99,true
2,Gadget,24.99,false

// Naive CSV parse — all strings (wrong types)
[
  { id: "1", name: "Widget", price: "9.99", active: "true" },
  { id: "2", name: "Gadget", price: "24.99", active: "false" }
]

// With type coercion
function coerce(value) {
  if (value === '') return null;
  if (value === 'true') return true;
  if (value === 'false') return false;
  if (!isNaN(Number(value)) && value !== '') return Number(value);
  return value;
}

// Using Papa Parse (browser + Node.js)
import Papa from 'papaparse';

const result = Papa.parse(csvString, {
  header: true,
  dynamicTyping: true,   // auto-converts numbers and booleans
  skipEmptyLines: true
});
// result.data = [{ id: 1, name: 'Widget', price: 9.99, active: true }, ...]