Convert Flat Parquet Rows to Nested JSON with DuckDB

Flat in, nested out

Parquet exports from warehouses are usually flat: one row per order line, with the order and customer fields repeated on every row. The API, document store or front-end that consumes the data wants the opposite shape:

{"order_id": 1001,
 "customer": {"name": "Aiko", "email": "aiko@example.com"},
 "items": [{"sku": "A-1", "qty": 2}, {"sku": "B-7", "qty": 1}]}

A plain Parquet to JSON conversion keeps the flat shape — one object per row. To nest, you regroup the rows first. In DuckDB that takes two building blocks: struct literals for objects and list() for arrays.

Objects: struct literals

{'name': customer_name, 'email': customer_email} builds a struct, and a struct serialises as a JSON object:

SELECT order_id,
       {'name': customer_name, 'email': customer_email} AS customer
FROM 'orders.parquet';

Arrays: list() with GROUP BY

To collect child rows into an array, group by the parent key and aggregate structs:

COPY (
  SELECT order_id,
         list({'sku': sku, 'qty': qty, 'unit_price': unit_price}
              ORDER BY sku) AS items
  FROM 'order_lines.parquet'
  GROUP BY order_id
  ORDER BY order_id
) TO 'orders.json' (FORMAT json, ARRAY true);

Output:

[
  {"order_id":1001,"items":[{"sku":"A-1","qty":2,"unit_price":9.5},{"sku":"B-7","qty":1,"unit_price":24.0}]},
  {"order_id":1002,"items":[{"sku":"A-1","qty":5,"unit_price":9.5}]}
]

The ORDER BY inside list() makes the array order deterministic; without it, element order depends on how the file was scanned.

Parent and child in separate files

Real data often has orders in one file and lines in another. Join, then group. GROUP BY ALL groups by every non-aggregated column so you do not have to repeat them:

COPY (
  SELECT o.order_id,
         o.order_date,
         {'name': o.customer_name, 'email': o.customer_email} AS customer,
         coalesce(
           list({'sku': l.sku, 'qty': l.qty, 'unit_price': l.unit_price}
                ORDER BY l.sku)
             FILTER (WHERE l.sku IS NOT NULL),
           []
         ) AS items
  FROM 'orders.parquet' o
  LEFT JOIN 'order_lines.parquet' l USING (order_id)
  GROUP BY ALL
  ORDER BY o.order_id
) TO 'orders.jsonl' (FORMAT json);

The FILTER and coalesce are not decoration. Without them, an order with no lines comes out as "items":[{"sku":null,"qty":null,"unit_price":null}] — one phantom item that will break any consumer counting items. With them it is "items":[].

Leaving out ARRAY true gives JSONL, one order per line. Prefer that for anything large: consumers can stream it, and a truncated file loses only its last line instead of becoming invalid JSON.

Types to watch

• Dates and timestamps become ISO strings ("2026-09-01"), which is usually what you want. Use strftime if the consumer expects another format.
• DECIMAL becomes a JSON number, so 9.50 turns into 9.5. For money sent to systems that parse numbers as doubles, emit CAST(unit_price AS VARCHAR) instead.
• 64-bit integers are written exactly, but JavaScript parses numbers above 2**53 lossily. Cast large IDs to strings if a browser reads the file.

Check the result

Read the JSON back and compare against the source:

SELECT count(*) AS orders, sum(len(items)) AS lines
FROM read_json('orders.jsonl');

SELECT (SELECT count(*) FROM 'orders.parquet')      AS orders,
       (SELECT count(*) FROM 'order_lines.parquet') AS lines;

DESCRIBE SELECT * FROM read_json('orders.jsonl') should show customer as a STRUCT and items as a STRUCT(...)[]. If items is JSON or VARCHAR, the nesting did not survive.

Where to run it

You can build and preview the nested SELECT in the SQL Workbench — drop both Parquet files and the struct and list columns render in the result grid, all in your browser. Run the final COPY with the DuckDB CLI to write the JSON file. If you need the opposite direction, turning nested columns into flat CSV, see flattening nested Parquet columns.