Flatten Nested JSON Arrays into Queryable Rows

1. Inspect the nesting shape before writing any flatten logic.

jq -c '.[0] | paths(type == "array")' orders.json | head -8

Expected output: Array paths print such as ["items"] and ["items",0,"discounts"], mapping exactly where flattening must happen.

2. Flatten one level with parent context attached.

jq -c '.[] | . as $o | .items[] | {orderId: $o.id, orderDate: $o.date, sku: .sku, qty: .qty, lineTotal: .total}' orders.json | head -3

Expected output: One JSON line per item prints, each carrying the order fields it came from, ready for CSV conversion or database load.

3. Handle the second nesting level without losing provenance.

jq -c '.[] | . as $o | .items[] | . as $i | (.discounts // [])[]? | {orderId: $o.id, sku: $i.sku, discountCode: .code, discountAmount: .amount}' orders.json | head -3

Expected output: Discount rows appear with both order and item context, and items with no discounts produce zero rows instead of null noise.

4. Stream large files in Node without loading them whole.

import { createReadStream } from 'node:fs';
import { createInterface } from 'node:readline';

async function* flatRows(file) {
  const rl = createInterface({ input: createReadStream(file), crlfDelay: Infinity });
  for await (const line of rl) {
    const order = JSON.parse(line);
    for (const item of order.items ?? []) {
      for (const disc of item.discounts ?? []) {
        yield { orderId: order.id, sku: item.sku, code: disc.code, amount: disc.amount };
      }
      if (!item.discounts?.length) {
        yield { orderId: order.id, sku: item.sku, code: null, amount: 0 };
      }
    }
  }
}

for await (const row of flatRows(process.argv[2] ?? 'orders.json')) {
  process.stdout.write(JSON.stringify(row) + '\n');
}

Expected output: The streaming version processes gigabyte-scale JSONL with constant memory, emitting the same flat rows as the jq prototype.

5. Verify the transform with an invariant: parent sums equal child sums.

jq -s '{orders: length, items: (map(.items) | add | length)}' orders.json; jq -s 'length' flat.jsonl; jq -s 'map(.qty) | add' flat.jsonl; jq -s 'map(.items | map(.qty) | add) | add' orders.json

Expected output: Both totals match across raw and flattened forms, proving the transform neither dropped nor duplicated rows.

Problem

Some rows come out with null where data existed

Solution

The array path was mistyped or optional at some documents; inspect paths again per document variant and use // [] defaults so missing arrays yield nothing rather than null errors.

Problem

jq runs out of memory on a huge file

Solution

jq -s slurps everything; switch to streaming with jq -c processing line by line (JSONL input), or move to the Node streaming generator for non-line-delimited files.

Problem

Spreadsheet import mangles the flat rows

Solution

Unicode or embedded commas need proper CSV quoting; emit TSV or JSONL instead and let the analytics tool ingest natively.