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".
- Author
- estuary ↗
- Repository
- agent-skills ↗
- Licence
- Apache-2.0
- Last repository change
- 24.09.2026
Assessment and security
- Files in this package
- 1
- Scripts
- 0
- HTML comments
- 0
- URLs in SKILL.md
- 3
No signals detected. Repository changed on 24.09.2026, without references to retired models or APIs.
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
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
- When to use this over alternatives
- Canonical Example — TypeScript (recommended)
- Canonical Example — SQL
- Test
- Variations
- Gotchas specific to flatten-array
- Related
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",…