derivation-flatten-array

derivation-flatten-array

Stateless Estuary derivation that emits many output documents from a single source document by iterating over a nested array.

Prereq: read derivation-basics first for concepts, project layout, workflow, and language choice.

Docs: https://docs.estuary.dev/guides/flatten-array/ — the official TypeScript walkthrough for this exact pattern. Read it alongside this skill.

When to use this over alternatives

• Normalising nested JSON: {order_id, line_items: [...]} → one doc per line item
• Exploding tags/categories: {article_id, tags: ["a", "b", "c"]} → one doc per tag
• Unpacking array columns from source databases for analytics
• Preparing data for systems that prefer normalised 1-row-per-item tables

If the array isn't in the source document itself (e.g., you need to join across collections to assemble it), use derivation-join-collections. If you just need to keep the array intact but change how it's materialised (e.g., MongoDB flow_document), you don't need a derivation at all.

Canonical Example — TypeScript (recommended)

Orders arrive with an embedded array of line items. We emit one document per line item, carrying order context (customer, date) onto each row.

Project layout

my-derivation/
├── flow.yaml
├── schema.yaml
└── flatten.ts

schema.yaml

The output schema describes one line item — not the array:

type: object
properties:
  order_id:
    type: string
  line_id:
    type: string
  customer_id:
    type: string
  order_date:
    type: string
    format: date-time
  product_id:
    type: string
  product_name:
    type: string
  quantity:
    type: integer
  unit_price:
    type: number
  line_total:
    type: number
required: [order_id, line_id, product_id]

flow.yaml

collections:
  acmeCo/normalized/order-line-items:
    writeSchema: schema.yaml
    readSchema:
      allOf:
        - $ref: flow://write-schema
        - $ref: flow://inferred-schema
    key: [/order_id, /line_id]
    derive:
      using:
        typescript:
          module: flatten.ts
      transforms:
        - name: flattenLineItems
          source: acmeCo/production/orders
          shuffle: any