Working With JSON Data: Formatting, Validation and the Trip to CSV

An export from an application usually arrives as a single unbroken line of text. It cannot be read, errors cannot be located, and pasting it into a spreadsheet achieves nothing. That is what handling JSON data looks like most days: make it readable, confirm it is valid, then move the records into whatever shape you actually need. Let us take those three steps in order.

The tools are unremarkable and that is the point. The JSON formatter handles indentation and syntax checking, JSON to CSV moves records into a table, and CSV to JSON goes the other way. When a payload carries encoded binary, the Base64 encoder and decoder opens it, and the URL encoder and decoder makes address fields readable.

Formatting JSON data: turning one line into something readable

Indentation changes nothing about the content; it only adds whitespace. The effect is still dramatic. Nesting depth becomes visible, a missing closing brace jumps out, and it stops being a puzzle which field belongs to which object. Payloads travelling to production should stay minified, but anything you are about to inspect deserves a moment of formatting first.

The one thing to watch while formatting is character encoding. Text containing non-ASCII characters looks mangled when opened under the wrong encoding, and the fault lies with the reader, not the file. Open the same content correctly and it is fine.

Key order and readability

The order of fields inside an object carries no technical meaning; software produces the same result whatever order they arrive in. For a human reader it matters a great deal. In output you intend to read by eye, putting identifiers first and long text bodies last lets you scan the same content twice as fast.

Validation: why JSON data breaks

Almost every syntax error falls into one of a few patterns. Learning to read the parser message beats searching the file at random, because the message hands you a line and column and you only have to look backwards from there.

• Trailing comma. A comma after the final element is the most common fault by a wide margin, and in hand-edited files it is nearly the norm.
• Single quotes. Keys and string values require double quotes. Single quotes are valid in other languages, not here.
• Unescaped quotes. A double quote inside a string, written without an escape, closes the value early and derails everything after it.
• Comments. The format has none. Explanatory lines added to configuration files invalidate them.
• Invisible characters. Text copied out of a web page can carry non-breaking spaces that look identical to ordinary ones.

If the error message names a line, go straight there. If it does not, cut the file in half and test which half parses; that finds a fault in a hundred-line file in about seven attempts.

Valid is not the same as correct

Passing a syntax check says nothing about whether the payload contains the fields you expect. A numeric field may have arrived as a string, a required key may be missing, a date may use a different convention. A short list helps when checking by hand: required fields, expected types, and values that must not be empty.

Moving records into a table

This is where the mismatch bites. A table is flat: rows and columns. The data structure is a tree, and any field can hold another object or a list. A flat list of objects drops into a table without argument. Nested structures force a decision.

Shape of the payload	Fits a table?	What to do
List of flat objects	Directly	Each field becomes a column
Single nested object	Partly	Join sub-fields with dots
Array inside a field	No	Extract it into a second table
Objects with differing fields	Partly	Union of columns, empty cells
A bare scalar	Pointless	Nothing to tabulate

Going the other way, from a table back into structured records, the recurring problem is type loss. Every cell in a spreadsheet is text. A value like 007 loses its leading zeros the moment something decides it is a number, and dates get reinterpreted according to local conventions. Keeping identifiers and codes as strings ends that argument before it starts.

Field names and structural discipline

The expensive mistakes in data exchange are not syntactic, they are nominal. When two systems emit the same fact under different key names, the translation layer between them accumulates special cases until nobody dares touch it. The cure is a small dictionary written at the start: every field, its type, its unit, and whether it may be absent.

Three naming rules cover most of it. First, pick one casing convention and hold to it; seeing both customerName and customer_name in one payload is a sign the file passed through two different hands. Second, avoid abbreviations, because a field called ttl becomes a riddle within a week. Third, put the unit in the name: duration_seconds instead of duration prevents an arithmetic bug that would otherwise take an afternoon to find.

On structure, the usual failure is excess depth. An object nested five levels deep does not look organised; it just lengthens every piece of code that reaches into it. As a rule of thumb, if getting to a value takes more than three steps, the structure is too deep. Likewise, anything whose count will grow over time belongs in a list rather than in field names; a shape that gains a new key for every new value falls into disrepair within months.

Finally, absence. A missing field and a field present with an empty value mean different things, and consumers that conflate them produce silent errors. Decide once which is which and write it down. That single sentence prevents a very long bug hunt later.

Small habits that pay off

• Test on a sample. Instead of processing fifty thousand records, try the first hundred. Faults surface in seconds rather than minutes.
• Freeze your field names. When two systems produce the same records under different key names, the conversion layer fills up with special cases.
• Do not diff by eye. Comparing two exports with the text diff checker is faster and far more reliable.
• Watch the size. An indented file can be twice the weight of its minified form; strip the whitespace before sending it over a network.

Frequently asked questions

Why does the content look garbled in my browser?

Almost always character encoding. Even when the file was saved correctly, a reader that assumes a different encoding will mangle accented characters. Check the encoding setting where you opened it before editing the file itself.

Can I put comments in a configuration file?

The format does not allow them. The common workaround is to add the explanation as an ordinary field named something like _note that the consuming software ignores. Inelegant, but it works and keeps the file valid.

How should I handle very large payloads?

Opening hundreds of megabytes in a browser is not reasonable. In that situation, restructure the export so each line holds one independent record. That makes both processing and debugging dramatically easier.

What about embedded binary content?

Binary travelling inside a text format is encoded and looks like noise. Decode it before inspecting, and remember it inflates by roughly a third along the way, which makes it a poor transport for large attachments.

For the keys and identifiers that end up living in configuration files, see strong passwords and secure identifiers; if the data is destined for a link or a scannable card, the QR code usage guide covers that path.