Nested JSON to CSV in Python: One Row per Array Item

Define a row before flattening anything

The example contains two orders. Order 0007 has two items and order 0008 has none. We want a flat order-item export while retaining visibility of the empty order. The columns are order_id, customer_name, sku and quantity; the output therefore contains three rows.

That empty-order rule is a choice, not a universal JSON conversion rule. An item-only report might deliberately omit order 0008. A customer summary might instead require one row per order and an aggregated item count. Write the intended meaning down before running a converter.

Save the input and the complete converter

Save the sample as orders.json and the script below as flatten_orders.py in a working folder. Use Python 3; the script needs no third-party package. Run python flatten_orders.py orders.json output.csv and choose a new output filename, because the script refuses to overwrite an existing file.

The strict field checks make the example intentionally specific. If your JSON has another customer field or a second array, decide how it belongs in the output and update the schema and mapping together. Deleting the checks without reviewing those fields can hide data loss.

import csv, json, sys

def unique_object(pairs):
    obj = {}
    for key, value in pairs:
        if key in obj:
            raise ValueError(f"Duplicate JSON key: {key}")
        obj[key] = value
    return obj

def bad_constant(value):
    raise ValueError(f"Not a JSON number: {value}")

def fields(obj, names):
    if not isinstance(obj, dict) or set(obj) != set(names):
        raise ValueError(f"Expected exactly these keys: {names}")

if len(sys.argv) != 3:
    raise SystemExit("Usage: python flatten_orders.py orders.json output.csv")
with open(sys.argv[1], encoding="utf-8-sig") as f:
    orders = json.load(f, object_pairs_hook=unique_object,
                       parse_constant=bad_constant)
if not isinstance(orders, list):
    raise ValueError("Top level must be an array")
output, seen = [], set()
for order in orders:
    fields(order, ["order_id", "customer", "items"])
    key = order["order_id"]
    if not isinstance(key, str) or not key.strip() or key in seen:
        raise ValueError("Order IDs must be unique nonempty strings")
    seen.add(key)
    fields(order["customer"], ["name"])
    name = order["customer"]["name"]
    items = order["items"]
    if not isinstance(name, str) or not isinstance(items, list):
        raise ValueError("Customer name must be text; items must be an array")
    if not items:
        output.append([key, name, "", ""])
    for item in items:
        fields(item, ["sku", "quantity"])
        sku, qty = item["sku"], item["quantity"]
        if not isinstance(sku, str) or not sku.strip():
            raise ValueError("SKU must be a nonempty string")
        if type(qty) is not int or qty < 0:
            raise ValueError("Quantity must be a nonnegative integer")
        output.append([key, name, sku, qty])
with open(sys.argv[2], "x", encoding="utf-8", newline="") as f:
    writer = csv.writer(f, lineterminator="\r\n")
    writer.writerow(["order_id", "customer_name", "sku", "quantity"])
    writer.writerows(output)
print(f"Wrote {len(output)} rows from {len(orders)} orders")

Read the result using its declared schema

The first two output rows repeat the same order ID and customer name because they belong to the same order. Counting CSV rows now counts exported order-item rows, not orders. Count distinct order IDs separately if you need the order total.

IDs remain strings throughout this converter. Quantity is a nonnegative JSON integer for real items; a boolean is rejected even though some programming environments treat booleans as a numeric subtype. The blank quantity on the preserved empty order is a different case from an item with quantity zero.

order_id,customer_name,sku,quantity
0007,Mina,00123,2
0007,Mina,00456,1
0008,José,,

Do not expand unrelated arrays without a relationship rule

Imagine adding three shipment records to the order that already has two items. Expanding both arrays independently can create six item-shipment combinations. Unless every item belongs to every shipment, those six rows describe relationships the input did not establish.

Instead, export the arrays as separate tables linked by order ID, or use a documented item-to-shipment key. In Power Query, expanding a record into columns and expanding a list into rows are different operations. Inspect row counts after each expansion rather than only after the final load.

Check the boundaries of this small converter

The script accepts a top-level array with exactly the documented keys. Duplicate JSON object keys and nonstandard numeric constants are rejected. Order IDs must be unique, nonblank strings. An empty top-level array produces only the CSV header, which is a valid empty result for this schema.

The input and output are held in memory before writing, so this example is intended for modest exports. Larger or changing schemas need a designed streaming or tabular process. The script does not evaluate spreadsheet formulas, and it does not sanitize cell contents for every downstream spreadsheet policy.

Verify rows and values before importing the CSV

Confirm three rows, two distinct orders and the preserved identifiers 0007 and 00123. Read the generated CSV with a CSV parser when checking commas or line breaks inside names. Counting physical text lines is not a reliable record count when fields contain newlines.

The published script was executed on the synthetic example and rejection cases. No Power Query UI execution is claimed. Column Harbor does not accept JSON or flatten nested structures; run this script locally, then inspect the resulting CSV using the tool appropriate to your destination.

Related help: Count CSV records correctly when fields contain newlines