Converting JSON to CSV When the Data Is Nested With Arrays
Flatten nested JSON to CSV with pandas, Python or DuckDB: dotted columns, a row per array item, the order that vanishes and arrays written as Python text.
JSON nests and CSV does not. A document can hold an object inside an object and a list of objects inside that, and every document can have different keys. A CSV is rectangular, so converting means choosing a rectangle. There is no single right choice, and each has a cost that is easy to miss: rows that disappear, columns that multiply, values that change type. This article takes a three-document file with the usual features, converts it four ways, and counts what each way keeps.
The input
[
{"id": 1, "customer": {"name": "Ana", "address": {"city": "Lisbon", "zip": "01234"}},
"items": [{"sku": "A-1", "qty": 2, "price": 9.5}, {"sku": "B-2", "qty": 1, "price": 20}],
"tags": ["new", "vip"], "note": null},
{"id": 2, "customer": {"name": "O'Neil, Pat"}, "items": [], "tags": [], "note": "", "coupon": "SAVE5"},
{"id": 3, "customer": {"name": "Bo \"Bee\" Chen", "address": {"city": "Taipei", "zip": "10001"}},
"items": [{"sku": "C-3", "qty": 5}], "tags": ["a,b"], "note": "line one\nline two"}
]Order 1 has a nested customer with an address, two items and two tags, and a null note. Order 2 has no address, an empty list of items, no tags, an empty note and an extra key coupon that no other order has. Order 3 has an address, one item with no price, a tag that contains a comma and a note with a line break. In total, 3 orders, 3 items, 3 distinct customers.
json_normalize: objects flatten, arrays do not
pandas has a function for exactly this, json_normalize, and its defaults show what it does and does not do:
import json, sys
import pandas as pd
SRC = "/batches/csv-0001-0200/lab/data/conv/orders.json"
data = json.load(open(SRC, encoding="utf-8"))
print("1. json_normalize, defaults: dictionaries flatten, lists do not")
flat = pd.json_normalize(data)
print(list(flat.columns))
print(flat.to_csv(index=False))
print("2. One row per item with record_path (orders without items disappear)")
items = pd.json_normalize(data, record_path="items", meta=["id", ["customer", "name"]])
print(items.to_csv(index=False))
print("orders in the input:", len(data), "| distinct ids in the output:", items["id"].nunique())python lab/conv/t0151.py1. json_normalize, defaults: dictionaries flatten, lists do not
['id', 'items', 'tags', 'note', 'customer.name', 'customer.address.city', 'customer.address.zip', 'coupon']
id,items,tags,note,customer.name,customer.address.city,customer.address.zip,coupon
1,"[{'sku': 'A-1', 'qty': 2, 'price': 9.5}, {'sku': 'B-2', 'qty': 1, 'price': 20}]","['new', 'vip']",,Ana,Lisbon,01234,
2,[],[],,"O'Neil, Pat",,,SAVE5
3,"[{'sku': 'C-3', 'qty': 5}]","['a,b']","line one
line two","Bo ""Bee"" Chen",Taipei,10001,
2. One row per item with record_path (orders without items disappear)
sku,qty,price,id,customer.name
A-1,2,9.5,1,Ana
B-2,1,20.0,1,Ana
C-3,5,,3,"Bo ""Bee"" Chen"
orders in the input: 3 | distinct ids in the output: 2
3. explode keeps the order with no items (as an empty row)
id,customer.name,sku,qty,price
1,Ana,A-1,2.0,9.5
1,Ana,B-2,1.0,20.0
2,"O'Neil, Pat",,,
3,"Bo ""Bee"" Chen",C-3,5.0,
4. Arrays kept as one cell: JSON text, or a joined string
id,items,tags
1,"[{""sku"":""A-1"",""qty"":2,""price"":9.5},{""sku"":""B-2"",""qty"":1,""price"":20}]",new|vip
2,[],
3,"[{""sku"":""C-3"",""qty"":5}]","a,b"The first block of the output has eight columns. The nested object became dotted columns: customer.name, customer.address.city, customer.address.zip. The key that only order 2 has, coupon, became a column that is empty for the other two, and the missing address of order 2 left three empty cells. The zip 01234 kept its zero, since it was a string in the JSON.
The arrays are the problem. items and tags are still lists, and writing them to CSV used Python's repr:
1,"[{'sku': 'A-1', 'qty': 2, 'price': 9.5}, {'sku': 'B-2', 'qty': 1, 'price': 20}]","['new', 'vip']",...That text has single quotes, so it is not JSON, and a reader that tries to json.loads the cell fails. It is also a Python-specific spelling that will confuse everyone else. Nothing in the output warns about it.
One row per array element
If the question is about the items, the natural shape is a row per item with the order's fields repeated. record_path does that:
sku,qty,price,id,customer.name
A-1,2,9.5,1,Ana
B-2,1,20.0,1,Ana
C-3,5,,3,"Bo ""Bee"" Chen"That is the second block of the output. Three item rows, with the order's id and the customer's name copied onto each through meta. And a count that is easy to overlook: the last line of the block reads orders in the input: 3 | distinct ids in the output: 2. Order 2 is gone. Its items list is empty, so it produced no rows, and the conversion succeeded without a word. If the next step is a count of orders or a join back to the order table, you are now short one. This is the classic inner-join behavior, and it is the reason to count before and after.
Keeping the order with no items
explode expands a list column into rows and keeps the row for an empty list as a single row with a missing value. Then the item objects can be expanded into columns:
print("3. explode keeps the order with no items (as an empty row)")
long = flat.assign(item=flat["items"]).explode("item")
long = pd.concat([long.drop(columns=["items", "item"]), pd.json_normalize(long["item"].apply(lambda x: x if isinstance(x, dict) else {})).set_index(long.index)], axis=1)
print(long[["id", "customer.name", "sku", "qty", "price"]].to_csv(index=False))Block 3 of the output has four rows: order 1 twice, order 2 once with empty sku, qty and price, and order 3. All three orders are present. The cost shows in the types: qty is 2.0, 1.0 and 5.0. pandas stored the column as float because one value is missing, and the CSV carries the .0. A reader who treats qty as an integer needs to cast, and the safe way is to write the integer columns with the nullable Int64 type before saving. Row arithmetic helps: 3 orders, 3 items and 1 empty order gives 4 rows, and the output has 4.
Arrays in one cell
If the consumer wants one row per document and the arrays are not the point, keep them in a single cell. Two ways:
print("4. Arrays kept as one cell: JSON text, or a joined string")
cell = flat.assign(items=[json.dumps(d["items"], separators=(",", ":")) for d in data],
tags=["|".join(d["tags"]) for d in data])
print(cell[["id", "items", "tags"]].to_csv(index=False))Block 4 shows both. The items array is written as JSON text with the quotes doubled for CSV, "[{""sku"":""A-1"",...}]", and it is valid JSON once the CSV layer is removed, so a downstream program can parse it. The tags are joined with a pipe: new|vip, and order 3's single tag a,b is kept intact (quoted, because it contains a comma). Joining is only safe when the joiner cannot occur in the data. A tag that contains | would make the cell ambiguous, so choose a separator that is excluded by the contract, or use JSON.
Wide format: a column per element
Another option is to put the first item in items.0.*, the second in items.1.*, and so on, with a recursive function:
import json, sys
def flatten(value, prefix="", out=None):
"""Dictionaries become dotted names; lists become name.0, name.1 (wide), scalars stay."""
out = {} if out is None else out
if isinstance(value, dict):
for k, v in value.items():
flatten(v, f"{prefix}.{k}" if prefix else k, out)
elif isinstance(value, list):
for i, v in enumerate(value):
flatten(v, f"{prefix}.{i}", out)
if not value:
out[prefix] = ""
else:
out[prefix] = "" if value is None else value
return out
rows = [flatten(d) for d in json.load(open(sys.argv[1], encoding="utf-8"))]
cols = []
for r in rows:
for k in r:
if k not in cols:
cols.append(k)
print(len(cols), "columns:", cols)
import csv
w = csv.DictWriter(sys.stdout, fieldnames=cols, restval="", lineterminator="\n")
w.writeheader(); w.writerows(rows)python lab/conv/flat.py lab/data/conv/orders.json16 columns: ['id', 'customer.name', 'customer.address.city', 'customer.address.zip', 'items.0.sku', 'items.0.qty', 'items.0.price', 'items.1.sku', 'items.1.qty', 'items.1.price', 'tags.0', 'tags.1', 'note', 'items', 'tags', 'coupon']
id,customer.name,customer.address.city,customer.address.zip,items.0.sku,items.0.qty,items.0.price,items.1.sku,items.1.qty,items.1.price,tags.0,tags.1,note,items,tags,coupon
1,Ana,Lisbon,01234,A-1,2,9.5,B-2,1,20,new,vip,,,,
2,"O'Neil, Pat",,,,,,,,,,,,,,SAVE5
3,"Bo ""Bee"" Chen",Taipei,10001,C-3,5,,,,,"a,b",,"line one
line two",,,For three documents the output has 16 columns: items.0.sku, items.0.qty, items.0.price, items.1.sku, ... tags.0, tags.1. The width is set by the longest array, so with real data it can reach hundreds of columns and varies from run to run, which breaks any schema. Wide format is acceptable when arrays have a small fixed bound (two phone numbers) and a poor choice otherwise. Note the two columns named items and tags at the end: they hold the empty lists of order 2 as empty strings, a special case the function handles by writing the name with nothing under it. That is a decision worth making explicitly.
DuckDB
DuckDB reads JSON with its types intact (read_json is documented in its JSON section) and has the tools for each of the shapes:
duckdb -box -c "DESCRIBE SELECT * FROM read_json('lab/data/conv/orders.json')"
duckdb -csv -c "SELECT id, customer.name AS customer_name, customer.address.city AS city, customer.address.zip AS zip, tags, coupon FROM read_json('lab/data/conv/orders.json')"┌─────────────┬───────────────────────────────────────────────────────────────────┬──────┬──────┬─────────┬───────┐
│ column_name │ column_type │ null │ key │ default │ extra │
├─────────────┼───────────────────────────────────────────────────────────────────┼──────┼──────┼─────────┼───────┤
│ id │ BIGINT │ YES │ NULL │ NULL │ NULL │
│ customer │ STRUCT("name" VARCHAR, address STRUCT(city VARCHAR, zip VARCHAR)) │ YES │ NULL │ NULL │ NULL │
│ items │ STRUCT(sku VARCHAR, qty BIGINT, price DOUBLE)[] │ YES │ NULL │ NULL │ NULL │
│ tags │ VARCHAR[] │ YES │ NULL │ NULL │ NULL │
│ note │ VARCHAR │ YES │ NULL │ NULL │ NULL │
│ coupon │ VARCHAR │ YES │ NULL │ NULL │ NULL │
└─────────────┴───────────────────────────────────────────────────────────────────┴──────┴──────┴─────────┴───────┘
id,customer_name,city,zip,tags,coupon
1,Ana,Lisbon,01234,"[new, vip]",NULL
2,"O'Neil, Pat",NULL,NULL,[],SAVE5
3,"Bo ""Bee"" Chen",Taipei,10001,"['a,b']",NULLDESCRIBE shows the inferred schema: customer STRUCT("name" VARCHAR, address STRUCT(city VARCHAR, zip VARCHAR)), items STRUCT(sku VARCHAR, qty BIGINT, price DOUBLE)[], tags VARCHAR[]. Dot notation reaches into a struct, so customer.address.city is just a column in a query. A list prints as [new, vip]. The DuckDB shell in CSV mode prints a missing value as the word NULL; to write a file with empty cells, use COPY ... TO, which does. (The shell's -csv mode is a display format.)
For a row per item:
duckdb -csv -c "SELECT o.id, i.sku, i.qty, i.price FROM read_json('lab/data/conv/orders.json') o, unnest(o.items) AS t(i)"
echo --- with the empty order kept
duckdb -csv -c "SELECT o.id, i.sku, i.qty, i.price FROM read_json('lab/data/conv/orders.json') o LEFT JOIN LATERAL (SELECT unnest(o.items) AS i) ON true"id,sku,qty,price
1,A-1,2,9.5
1,B-2,1,20.0
3,C-3,5,NULL
--- with the empty order kept
id,sku,qty,price
1,B-2,1,20.0
3,C-3,5,NULL
1,A-1,2,9.5
2,NULL,NULL,NULLunnest gives the same three item rows as record_path, and again order 2 is missing. The LEFT JOIN LATERAL keeps it, with NULLs for the item columns, as the last block shows. Notice the order of the output rows: 1 (B-2), 3, 1 (A-1), 2. A query without ORDER BY promises no order, and DuckDB uses its parallelism. Add ORDER BY id and an item position if the order matters to the consumer.
Choosing a shape
| Shape | Rows | Good for | Cost |
|---|---|---|---|
| One row per document, arrays as JSON text | 3 | keeping the document count; later parsing | the consumer must parse the cell |
| One row per array element | 3 or 4 | joins, sums over items, long format | parent fields repeat; empty arrays vanish unless kept |
| Wide: a column per element | 3 | tiny fixed arrays | unbounded width; schema changes |
| Joined string | 3 | simple lists of safe tokens | ambiguity if the joiner occurs in data |
How to tell it worked
Do the row arithmetic before you look at the file: documents, elements per document, and how you handle empty arrays should add up to the row count. Compare distinct parent ids in the output with the input. Parse one JSON-in-a-cell value back. Check the types of columns that had missing values. Include a document that is missing keys, one with an empty array and one with a string that contains your delimiter, as these three did. And keep the flattening rule written next to the output, since the CSV cannot say how it was made.
When the structure is not a table
If every document has a different structure, the CSV will be mostly empty cells. If arrays are deeply nested (items with lists of options with lists of values), flattening them all at once produces a combinatorial number of rows; flatten to several related tables instead, a parent table, an item table keyed by the parent id, and so on, which is what the data really is. That is a database design question, and the JSON Lines and JSON articles cover the other direction, from CSV into JSON. For the full set of targets, the conversion hub compares them.
