Flattening a Shopify Style Order Export Into Tables

E-commerce platforms export orders as JSON, and the shape is predictable enough that you can plan for it. An order is an object. Inside it are things that repeat (line items, shipping lines, discounts, refunds), things that may be missing (customer, shipping address) and things that look like numbers and are strings (prices, taxes). A spreadsheet or a BI tool wants none of that. It wants an orders table, a line items table, and perhaps a refunds table, joined by the order id.

I generated an export shaped like the order objects of a typical platform's API, 20,000 orders in one JSON document, and flattened it with pandas and with DuckDB. The data is synthetic, written to follow the public structure of an order object, so the figures are properties of my file.

What the document looks like

An order, trimmed:

{"id": 4000000000001, "name": "#1001", "email": null,
 "created_at": "2026-03-01T00:03:00-05:00", "currency": "USD",
 "financial_status": "paid", "fulfillment_status": null,
 "subtotal_price": "49.95", "total_tax": "1.20", "tags": "vip, wholesale",
 "customer": {"id": 3012, "first_name": "A", "last_name": "B", "orders_count": 7, "tags": "vip"},
 "shipping_address": {"city": "Austin", "country_code": "US", "zip": "73301"},
 "line_items": [{"id": 10000000000010, "sku": "SKU-0042", "title": "Mug", "quantity": 2,
                 "price": "19.50", "total_discount": "0.00", "properties": []}],
 "shipping_lines": [{"title": "Standard", "price": "5.00"}],
 "discount_applications": [], "refunds": []}

Three kinds of fields need three kinds of treatment. Scalars stay on the orders table. Nested objects (customer, shipping_address) become prefixed columns. Arrays of objects (line_items, shipping_lines, refunds) become their own tables with the order id carried along.

With pandas

d = json.load(open("shop.json"))["orders"]
orders = pd.json_normalize(d, sep="_", max_level=1).drop(
    columns=["line_items", "shipping_lines", "discount_applications", "refunds"])
li = pd.json_normalize(d, record_path="line_items",
                       meta=["id", "created_at", "currency"], meta_prefix="order_", sep="_")

Both calls together took 1.42 s. The orders frame was 20,000 rows and 20 columns, with customer_id, customer_first_name, customer_orders_count, shipping_address_city and so on. The line items frame had 42,937 rows. record_path names the array to expand, and meta copies the order-level fields onto each row (the prefix keeps order_id from colliding with the line item's own id). max_level=1 stops the normalizer from going deeper than one level, which keeps properties (a list) as a list.

One oddity remained: with a null customer the orders frame also had a bare customer column, all missing, next to the prefixed ones. When some rows have the object and some have null, json_normalize keeps the original key for the rows that had nothing to expand. Drop that column after checking it holds only nulls.

With DuckDB

create table o as
select unnest(orders, recursive := false)
from (select unnest(orders) as orders from read_json('shop.json', maximum_object_size=104857600));

create table li as
select o.id as order_id, unnest(o.line_items, recursive := true)
from (select unnest(orders) as o from read_json('shop.json', maximum_object_size=104857600));

The maximum_object_size setting matters for a single-document file: the default limit on one JSON value is 16 MB, and this file is 19 MB. The two queries took about 0.8 s and 0.7 s and produced the same 20,000 orders and 42,937 line items. DuckDB inferred types as it read: id as BIGINT, created_at as TIMESTAMP WITH TIME ZONE, customer as a struct, line_items as an array of structs, and the prices as VARCHAR. It kept customer and shipping_address as structs, so customer is null and shipping_address.city work directly.

Either tool is fine. Pandas wins when the next step is in Python. DuckDB is better when the file is larger than comfortable memory, because it streams, or when the output is going to a database or Parquet.

Money arrives as strings

All prices, discounts and taxes are strings: "19.50". Many platforms do this on purpose, to avoid floating point, and the first consequence is that nothing sums until you convert them. In pandas, pd.to_numeric makes floats. In DuckDB, cast to a decimal:

select count(*), sum(price::decimal(12,2) * quantity - total_discount::decimal(12,2)) as line_total from li;
-- 42937 | 4710106.58

The pandas version, with floats, also gave 4,710,106.58. For 43,000 two-digit values the float error stays under a cent. At a larger scale, or when you add many values and compare to a ledger, it does not, and the decimal type is the safe habit. I also checked that the orders' own subtotal_price summed to the same figure as price times quantity across the line items (4,766,341.08 on both sides, before discounts), which is the cross-check to run on any export: if the parts do not add up to the whole, you have lost rows or misread a field.

Keep the currency with the amount. In this export every order has one currency, and each line inherits it. In a multi-currency shop the line table needs a currency column, and the sum must group by it (normalizing a Stripe style webhook payload shows what goes wrong when you divide every currency by 100).

Null objects and missing relations

10,017 of 20,000 orders had a null customer (guest checkouts, in this data). 10,068 had no shipping city. Those are two different questions: an order with no customer object versus an order with a customer whose address is incomplete. In a flat table both look like null cells.

select count(*) filter (where customer is null) as null_customers,
       count(*) filter (where shipping_address.city is null) as no_city from o;
-- 10017 | 10068

Decide what you want before joining to a customer dimension: a left join, so the guest orders stay, and a stated rule for how to count customers (distinct non-null ids). A report of "orders per customer" that silently drops half the orders because of an inner join is the usual result of not asking.

Tags, dates and zip codes

Tags came as a single string, "vip, wholesale". Splitting on ", " and exploding gave vip 10,054, wholesale 4,921, gift 4,914 and 5,032 empty strings, because an order with no tags has "". Drop the empties, and strip whitespace, since a different export might separate with ",".

Dates carry the shop's offset. 2026-03-01T00:03:00-05:00 converted to UTC is 2026-03-01 05:03:00. Store timestamps in UTC and convert for display, or you will get a report in which a late-evening order belongs to tomorrow (serializing dates and decimals in JSON with Python covers the round trip). In pandas, pd.to_datetime(..., utc=True) does it. DuckDB inferred the type and showed 2026-03-01 05:03:00+00.

Zip codes are text. The sample had 01000-000, 100000 and 30-001. Reading them as numbers drops the leading zero and the hyphen. Force the column to a string type whenever a field is an identifier and not a quantity.

Refunds and other nested lists

A refund object holds a list of its own (refund_line_items), so refunds are two levels deep. Do the same trick twice: unnest the refunds with the order id, then unnest each refund's line items with the refund id, and keep both ids. An order that was partially refunded has a refund that references only one of its line items, and the refund's quantity does not exceed the line's. In my data, 7,906 orders (3,958 refunded and 3,948 partially refunded) each had one refund.

Check the join cardinality at every step: orders to line items is one to many, line items to refund lines is one to few, and joining all three at once multiplies rows. Aggregate each child table to the order level before you join them if you want an order-level summary.

Keep the raw file, and build the tables from it

The export is the source. Store it as it arrived and rebuild the tables from it when a rule changes. The DuckDB reader options are in the JSON overview, and pandas documents json_normalize in its API reference. For deeper nesting patterns see flattening nested JSON with pandas and DuckDB, and to look at one order, paste it into the JSON formatter. To flatten a document without code, the JSON flatten tool shows the paths.

When flattening loses what you needed

The nesting is the information. A product with variants, each with options, is a tree. A flat table repeats the parent on every leaf, and the question "how many products" becomes a distinct count.

Arrays have meaning in their order. If the first line item is the primary one, an unnest without an ordinal loses it. Keep an index column.

The document is already good enough for the query. For a single total, a path expression over the raw JSON is quicker than building tables.

The schema is unstable. If the platform adds fields every quarter, the flattening code changes with it. Keep the raw JSON in a column next to the flat fields, so nothing is lost when a new field matters.