Download the scripts and tests (ZIP)
Python scripts, tests, and ready-to-run merge, join, and date practice files with expected results. No signup required.
A JSON export can contain a customer object, a list of tags, and an optional note in each record. A useful CSV needs a decision about each of those structures. Expanding every list can multiply rows; turning every absent value into an empty cell hides the difference between an empty string and missing information.
This guide exports one CSV row per input object. You choose fields using lists of literal object keys. Each value gets a companion status column, and arrays stay as JSON text inside a single CSV cell. This is a selective export, not a reversible copy of the whole document.
Tested locally on Python 3.10 on Windows, September 16, 2026, using synthetic records and the tests in the downloadable package. No third-party libraries or account connections are needed.
Choose a predictable table shape
Save this as records.json in UTF-8:
[
{"id":"001","customer":{"name":"Mina"},"tags":["new","priority"],"note":null},
{"id":"002","customer":{},"tags":[],"note":""},
{"id":"003","customer":null,"tags":["repeat"]}
]
Save this separate mapping as fields.json:
{
"id": ["id"],
"name": ["customer", "name"],
"tags": ["tags"],
"note": ["note"]
}
The mapping keys become column names, in that order. The lists are paths through objects only. ["customer", "name"] means the name member inside customer. A literal key containing a dot stays one element: ["customer.name"]. An empty-string key is supported inside a nonempty path. These rules avoid inventing a dot-escaping language.
Understand the status columns
| Status | Meaning | Value cell |
|---|---|---|
string |
A JSON string, including an empty string | Original string |
json |
A number, boolean, array, or object | Compact JSON text |
null |
The selected final value is JSON null | Empty |
missing |
A requested key is absent from an object | Empty |
blocked |
A remaining path encounters a non-object | Empty |
For the third sample record, the path to customer.name is blocked because customer is null. By contrast, the final note value in the first record is null. In the second record customer is an object, but its name key is missing. Keeping these outcomes separate can make a later data-cleaning decision auditable.
The script does not index arrays or expand their elements. A path such as ["tags", "0"] is blocked when tags is an array; "0" is an object key, not a list index. Select ["tags"] to retain the complete list in one cell. If your real task needs one row per line item, define that row relationship separately before exporting.
Complete script
Save as nested_json_to_csv.py beside the two input files:
"""Export explicit object-key paths with a status column for every value."""
import argparse
import csv
import json
import sys
from pathlib import Path
def unique_object(pairs):
result = {}
for key, value in pairs:
if key in result:
raise ValueError('Duplicate JSON key: ' + key)
result[key] = value
return result
def invalid_constant(value):
raise ValueError('Non-standard JSON constant: ' + value)
def read_json(path):
with Path(path).open(encoding='utf-8-sig') as handle:
return json.load(handle, object_pairs_hook=unique_object,
parse_constant=invalid_constant)
def extract(record, path):
value = record
for key in path:
if not isinstance(value, dict):
return '', 'blocked'
if key not in value:
return '', 'missing'
value = value[key]
if value is None:
return '', 'null'
if isinstance(value, str):
return value, 'string'
# Containers stay in one CSV cell; no implicit row expansion.
return json.dumps(value, ensure_ascii=False, allow_nan=False,
separators=(',', ':')), 'json'
def export_nested(source, mapping, output):
fields = read_json(mapping)
if not isinstance(fields, dict) or not fields:
raise ValueError('Mapping must be a nonempty object of name: key-list')
headers = []
for name, path in fields.items():
if not name.strip() or not isinstance(path, list) or not path:
raise ValueError('Names and key paths must be nonempty')
if not all(isinstance(key, str) for key in path):
raise ValueError('Every path element must be an object key string')
headers.extend((name, name + '__status'))
if len(set(headers)) != len(headers):
raise ValueError('Output names collide with generated status columns')
records = read_json(source)
if not isinstance(records, list):
raise ValueError('Input must be a JSON array of objects')
rows = []
for number, record in enumerate(records, 1):
if not isinstance(record, dict):
raise ValueError('Record %d is not an object' % number)
row = []
for path in fields.values():
row.extend(extract(record, path))
rows.append(row)
# Serialize and encode before opening the destination, including Unicode checks.
import io
buffer = io.StringIO(newline='')
writer = csv.writer(buffer)
writer.writerow(headers)
writer.writerows(rows)
encoded = buffer.getvalue().encode('utf-8')
with Path(output).open('xb') as handle:
handle.write(encoded)
return len(rows)
def main():
parser = argparse.ArgumentParser(description=__doc__)
parser.add_argument('source', type=Path)
parser.add_argument('mapping', type=Path)
parser.add_argument('output', type=Path)
args = parser.parse_args()
try:
count = export_nested(args.source, args.mapping, args.output)
except (OSError, ValueError, RecursionError) as exc:
print('Error: ' + str(exc), file=sys.stderr)
return 1
print('Exported %d records' % count)
return 0
if __name__ == '__main__':
raise SystemExit(main())
Run:
python nested_json_to_csv.py records.json fields.json result.csv
The expected message is Exported 3 records. The output is UTF-8 CSV with a header and three data records. The 001 ID remains a string. For readability, the table below groups each value with its status; the real file has eight separate columns.
| ID | Name / status | Tags / status | Note / status |
|---|---|---|---|
| 001 | Mina / string | [“new”,”priority”] / json | empty / null |
| 002 | empty / missing | [] / json | empty / string |
| 003 | empty / blocked | [“repeat”] / json | empty / missing |
A CSV viewer may display doubled quotes around the JSON array. That is CSV quoting, not extra data. Read the cell with a CSV parser before passing its contents to json.loads.
Check the result with Python
Run this verification from the same directory:
import csv
import json
with open('result.csv', encoding='utf-8', newline='') as handle:
rows = list(csv.DictReader(handle))
assert len(rows) == 3
assert rows[0]['id'] == '001'
assert json.loads(rows[0]['tags']) == ['new', 'priority']
assert rows[0]['note__status'] == 'null'
assert rows[1]['note'] == '' and rows[1]['note__status'] == 'string'
assert rows[1]['name__status'] == 'missing'
assert rows[2]['name__status'] == 'blocked'
assert rows[2]['note__status'] == 'missing'
print('Sample checks passed')
The package tests also cover literal dot and empty keys, Unicode and embedded newlines, booleans, an empty input array, and the command’s success and failure exits. Rejection cases include duplicate JSON keys, non-standard constants, overflowing selected numeric values, invalid mapping shapes, generated-column collisions, malformed input, and attempts to overwrite either input or an existing output.
What the safeguards do
Duplicate object keys are rejected in both files, even outside the selected fields, so a later repeated key cannot silently replace an earlier value. NaN and Infinity constants are rejected. Output names must be nonblank and cannot collide with the generated __status names. Every top-level record must be an object.
The script reads and validates the input, prepares the rows, and encodes the CSV before opening the output. It opens the destination exclusively, so an existing output is an error. Input and mapping files are never edited. Use a fresh output filename for a new run. A missing parent directory is also an error; the script does not create it.
Successful conversion exits with code 0; handled processing failures exit with code 1. Argument usage errors are handled by argparse. Missing and blocked fields are reported in the CSV rather than treated as conversion failures. Review those status columns before using the file downstream.
Limits that affect real exports
This implementation holds the entire JSON document, output rows, and encoded CSV in memory. Use it for manageable local exports. It is not a streaming converter for large datasets, and deeply nested or oversized inputs can exhaust resources. A disk failure during the final write can leave a partial output; validation before writing does not make file storage transactional.
JSON numeric values use Python’s default number decoding. Floating-point values can lose decimal precision or change spelling, and an overflowing selected float is rejected during JSON serialization. Do not use this implementation as an exact-decimal financial converter. Preserve identifiers as JSON strings in the source; a number already lacking leading zeroes cannot restore them here. Unselected fields are omitted, not validated against a business schema.
CSV preserves the stored text, but spreadsheet software may infer dates or numbers or interpret strings beginning with formula characters. Import the relevant columns as text and review untrusted values before opening them in a spreadsheet. This script does not neutralize formulas or change source strings.
If your input is one JSON object per line instead of a top-level array, start with the JSONL validation guide. For the other direction, use CSV to JSON with text IDs preserved. Once the table is exported, the CSV column selection guide can help create a narrower downstream file.
References
Python’s JSON documentation documents decoding hooks, duplicate-key behavior, constants, and the mapping between JSON and Python types. Python’s CSV documentation explains writer quoting and opening CSV text files with newline=''. The path rules, status vocabulary, and rejection behavior in this guide are choices implemented and tested in the script above.