Skip to content
Skills Library
Data & analytics · Building & coding

derivation-flatten-array

/derivation-flatten-array

Create an Estuary derivation that flattens a nested array into one output document per array element. Use for normalising nested JSON, exploding line items, unpacking tags, or producing one-row-per-item tables from arrays. Derivations add complexity and cost — confirm the user wants a derivation before reaching for this (some destinations can `UNNEST` server-side without one). Use when user says "flatten-array derivation", "derivation to explode an array", "transformation to unnest", "derive one row per item", or "flatten nested data with a derivation".

Licence
Apache-2.0
Last repository change
24.09.2026

Assessment and security

100/ 100Class: good
Format25 / 25
Frontmatter with name and description, valid name, description up to 1,024 characters, name matching the folder.
Discoverability25 / 25
Description explains when to use the skill, has a reasonable length and uses third person.
Content35 / 35
At least 150 words and at most 500 lines, headings, step lists, examples or code.
Hygiene15 / 15
No placeholders such as TODO, no Windows paths, with a licence.
Riskno signals detected in the scan
Files in this package
1
Scripts
0
HTML comments
0
URLs in SKILL.md
3
Deprecation

No signals detected. Repository changed on 24.09.2026, without references to retired models or APIs.

Licence

Apache-2.0, otwarta: można używać i zmieniać, z zachowaniem licencji i informacji o zmianach.

What it does and when to use it

Create an Estuary derivation that flattens a nested array into one output document per array element. Use for normalising nested JSON, exploding line items, unpacking tags, or producing one-row-per-item tables from arrays. Derivations add complexity and cost — confirm the user wants a derivation before reaching for this (some destinations can `UNNEST` server-side without one). Use when user says "flatten-array derivation", "derivation to explode an array", "transformation to unnest", "derive one row per item", or "flatten nested data with a derivation".

Triggers from the description

flatten-array derivationderivation to explode an arraytransformation to unnestderive one row per itemflatten nested data with a derivation
Original author description

Create an Estuary derivation that flattens a nested array into one output document per array element. Use for normalising nested JSON, exploding line items, unpacking tags, or producing one-row-per-item tables from arrays. Derivations add complexity and cost — confirm the user wants a derivation before reaching for this (some destinations can `UNNEST` server-side without one). Use when user says "flatten-array derivation", "derivation to explode an array", "transformation to unnest", "derive one row per item", or "flatten nested data with a derivation".

How it works

  1. When to use this over alternatives
  2. Canonical Example — TypeScript (recommended)
  3. Canonical Example — SQL
  4. Test
  5. Variations
  6. Gotchas specific to flatten-array
  7. Related
Original SKILL.md headings · 239 lines, 1,176 words
SKILL.md preview
---
name: derivation-flatten-array
description: Create an Estuary derivation that flattens a nested array into one output document per array element. Use for normalising nested JSON, exploding line items, unpacking tags, or producing one-row-per-item tables from arrays. Derivations add complexity and cost — confirm the user wants a derivation before reaching for this (some destinations can `UNNEST` server-side without one). Use when user says "flatten-array derivation", "derivation to explode an array", "transformation to unnest", "derive one row per item", or "flatten nested data with a derivation".
---

# 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:

```yaml
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`

```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
```

Key fields: the collection key is composite (`order_id` + `line_id`) — the array element id keeps output rows unique. `shuffle: any` because flattening is stateless. The `writeSchema` declares what the lambda emits; `readSchema` lets the inferred schema widen automatically as the lambda evolves. See `derivation-basics` § Schema setup.

### `flatten.ts`

Generate the stub with `flowctl generate --source flow.yaml`, then fill in:

```typescript
import { IDerivation, Document, SourceFlattenLineItems } from 'flow/acmeCo/normalized/order-line-items.ts';

export class Derivation extends IDerivation {
  flattenLineItems(_read: { doc: SourceFlattenLineItems }): Document[] {
    const order = _read.doc;

    // Missing or empty array → emit nothing
    if (!order.line_items || order.line_items.length === 0) {
      return [];
    }

    return order.line_items.map(item => ({
      order_id: order.order_id,
      line_id: item.line_id,
      customer_id: order.customer_id,
      order_date: order.order_date,
      product_id: item.product_id,
      product_name: item.product_name,
      quantity: item.quantity,
      unit_price: item.unit_price,
      line_total: item.quantity * item.unit_price,
    }));
  }
}
```

Return `[]` to drop the document entirely; return one document per array element to flatten.

## Canonical Example — SQL

SQLite can flatten via `json_each`, which produces one row per array element. Works well for shallow arrays of primitives or simple objects; gets verbose for deeply nested structures (TypeScript is usually clearer then).

### Tags array (simple case)

```yaml
collections:
  acmeCo/normalized/article-tags:
    schema:
      type: object
      properties:
        article_id: { type: string }
        tag: { type: string }
      required: [article_id, tag]
    key: [/article_id, /tag]
    derive:
      using:
        sqlite: {}
      transforms:
        - name: flattenTags
          source: acmeCo/articles
          shuffle: any
          lambda: |
            SELECT
              $article_id AS article_id,
              json_each.value AS tag
            FROM json_each($tags);
```

### Object array (more verbose)

```yaml
lambda: |
  SELECT
    $order_id AS order_id,
    json_extract(json_each.value, '$.line_id')      AS line_id,
    json_extract(json_each.value, '$.product_id')   AS product_id,
    json_extract(json_each.value, '$.product_name') AS product_name,
    CAST(json_extract(json_each.value, '$.quantity') AS INTEGER)   AS quantity,
    json_extract(json_each.value, '$.unit_price')   AS unit_price
  FROM json_each($line_items);
```

For anything beyond simple fields, prefer the TypeScript form.

## Test

### Local validation — `flowctl preview --fixture` (works today)

`fixture.jsonl`:

```json
["acmeCo/production/orders", {"order_id": "o1", "customer_id": "c1", "order_date": "2024-06-01T10:00:00Z", "line_items": [{"line_id": "li_1", "product_id": "p_a", "product_name": "Widget", "quantity": 2, "unit_price": 10.00}, {"line_id": "li_2", "product_id": "p_b", "product_name": "Gadget", "quantity": 1, "unit_price": 25.00}, {"line_id": "li_3", "product_id": "p_c", "product_name": "Gizmo", "quantity": 3, "unit_price": 5.50}]}]
["acmeCo/production/orders", {"order_id": "o2", "customer_id": "c2", "order_date": "2024-06-02T10:00:00Z", "line_items": []}]
{"commit": true}
```

```bash
flowctl preview --source flow.yaml \
  --name acmeCo/normalized/order-line-items \
  --fixture fixture.jsonl --timeout 30s
```

Expected output (3 docs from o1, nothing from o2's empty array):

```json
{"order_id":"o1","line_id":"li_1","customer_id":"c1","order_date":"2024-06-01T10:00:00Z","product_id":"p_a","product_name":"Widget","quantity":2,"unit_price":10.00,"line_total":20.00}
{"order_id":"o1","line_id":"li_2","customer_id":"c1","order_date":"2024-06-01T10:00:00Z","product_id":"p_b","product_name":"Gadget","quantity":1,"unit_price":25.00,"line_total":25.00}
{"order_id":"o1","line_id":"li_3","customer_id":"c1","order_date":"2024-06-01T10:00:00Z","product_id":"p_c","product_name":"Gizmo","quantity":3,"unit_price":16.50}
```

### Spec-defined tests — `tests:` block

```yaml
tests:
  acmeCo/tests/flatten-line-items:
    - ingest:
        collection: acmeCo/production/orders
        documents:
          - order_id: "o1"
            customer_id: "c1"
            order_date: "2024-06-01T10:00:00Z"
            line_items:
              - { line_id: "li_1", product_id: "p_a", product_name: "Widget", quantity: 2, unit_price: 10.00 }
              - { line_id: "li_2", product_id: "p_b", product_name: "Gadget", quantity: 1, unit_price: 25.00 }
              - { line_id: "li_3", product_id: "p_c", product_name: "Gizmo",  quantity: 3, unit_price: 5.50 }
          - order_id: "o2"
            customer_id: "c2"
            order_date: "2024-06-02T10:00:00Z"
            line_items: []
    - verify:
        collection: acmeCo/normalized/order-line-items
        documents:
          - { order_id: "o1", line_id: "li_1", customer_id: "c1", order_date: "2024-06-01T10:00:00Z", product_id: "p_a", product_name: "Widget", quantity: 2, unit_price: 10.00, line_total: 20.00 }
          - { order_id: "o1", line_id: "li_2", customer_id: "c1", order_date: "2024-06-01T10:00:00Z", product_id: "p_b", product_name: "Gadget", quantity: 1, unit_price: 25.00, line_total: 25.00 }
          - { order_id: "o1", line_id: "li_3", customer_id: "c1", order_date: "2024-06-01T10:00:00Z", product_id: "p_c", product_name: "Gizmo",  quantity: 3, unit_price: 5.50,  line_total: 16.50 }
```

## Variations

- **Simple string array (tags, prefs):** `json_each($tags)` in SQL, `_read.doc.tags.map(tag => ({...}))` in TS.
- **JSON-string-encoded array:** wrap `JSON.parse` in try/catch for TS; `json_each(json($field))` in SQL where `$field` is a string.
- **Deeply nested arrays (categories→products):** nested `for...of` loops in TS. SQL gets painful fast — prefer TS.
- **Conditional flattening:** filter array elements before emitting (`_read.doc.addresses.filter(a => a.is_primary).map(...)`).
- **Include array index as a field:** TS has `.map((item, i) => ...)`; SQL uses `json_each.key` as the index.
- **Partial re-emit on upstream update:** if the source document's array shrinks on update, stale output documents remain. See the gotcha below.

## Gotchas specific to flatten-array

- **Key uniqueness is on you.** The derived collection key must uniquely identify a flattened row. If the array elements have stable IDs (`line_id`), use them. If they don't, include the array index (`item_index`) in the key — otherwise multiple elements collide and reduce into one.
- **Source updates don't remove stale flattened rows.** If an upstream order initially had 3 line items and the array later shrinks to 2, the derivation re-runs on the source update and emits 2 new rows — but the 3rd earlier row stays in the derived collection. If you need strict 1:1 sync with the source array, model deletions explicitly (e.g., source emits full array each update and you compare; or include a `deleted` flag per line item).
- **Empty/missing array is not an error.** `return []` in TS or `FROM json_each(NULL)` in SQL both produce zero output docs, which is the right behaviour — but surprises people expecting a per-source-doc output. The source document itself is not reflected in the derived collection.
- **`json_each` behavior on non-array input.** Only `json_each(NULL)` and `json_each('[]')` silently yield zero rows. A JSON object yields one row per member, a scalar (`json_each(42)`) yields a single row, and a non-JSON string raises a `malformed JSON` error that surfaces as a transform failure. If you hit "no output", inspect the source with `flowctl preview --source flow.yaml --name <source-collection>` to see what the field actually contains.

## Related

- `derivation-basics` — prerequisite
- `derivation-filter-transform` — when you want zero-or-one output per input, not many
- `derivation-join-collections` — when the array you want to flatten is spread across multiple source collections
- [How to Flatten an Array Using TypeScript (docs walkthrough)](https://docs.estuary.dev/guides/flatten-array/)
- [Derivations concept](https://docs.estuary.dev/concepts/derivations/)

Versions

The database has no other skills named derivation-flatten-array.

Database snapshot: 13.11.2026 · package version bcb9cce403c6 (SHA-256 digest of the entire package)

Downloads in the last 30 days: 0 · ranking

Report a problem with this skill: harmful content, incorrect descriptions or an author’s removal request.

Similar skills

  • 100derivation-stateful-logic
    Create an Estuary derivation with custom SQLite state (internal tables) to make per-document decisions based on accumulated history — account balances, inventory levels, approval workflows, deduplication. Derivations add complexity and cost — confirm the user wants a derivation…
  • 100derivation-filter-transform
    Create an Estuary derivation for stateless filtering, field selection, and per-document field transformation. Use for WHERE clauses, projecting fields, computing new fields from existing ones, or data cleansing. Derivations add complexity and cost — confirm the user wants a…
  • 100jq-json-parsing
    Queries, filters, counts and reshapes JSON files and API output from the command line with jq. Use when the user wants to dig through large or nested JSON, for example "pull every name out of this JSON array with jq", "count the log entries where latency is over a threshold",…

Feedback and reports

Your message goes to Bartosz Sokoliński. An email address is needed only for a reply. See the privacy policy.