Keyboard shortcuts

Press ← or → to navigate between chapters

Press S or / to search in the book

Press ? to show this help

Press Esc to hide this help

Split and unnest

A split writes one source record into several tables. An unnest writes one row per element of an array. Together they turn a nested document into a set of related tables.

The worked example

Three MongoDB product documents, each with a nested dimensions object, an array of tags (strings) and an array of variants (objects), each variant with an array of options:

[
  {
    "_id": "p-1",
    "name": "Desk lamp",
    "price": { "$numberDecimal": "24.50" },
    "tags": ["lighting", "office"],
    "dimensions": { "width_cm": 15, "height_cm": 40 },
    "variants": [
      {
        "sku": "LAMP-BLK", "color": "black", "stock": 12,
        "options": [{ "name": "bulb", "value": "LED" }, { "name": "plug", "value": "EU" }]
      },
      {
        "sku": "LAMP-WHT", "color": "white", "stock": 0, "discontinued": true,
        "options": [{ "name": "bulb", "value": "LED" }]
      }
    ]
  },
  {
    "_id": "p-2",
    "name": "Notebook",
    "price": { "$numberDecimal": "3.99" },
    "tags": [],
    "variants": [
      { "sku": "NB-A5", "color": "red", "stock": 100, "discontinued": false, "options": [] }
    ]
  },
  {
    "_id": "p-3",
    "name": "Cable",
    "price": { "$numberDecimal": "7.125" },
    "tags": ["office"],
    "dimensions": null
  }
]

The edge cases: p-2 has an empty tags array and a variant marked discontinued: false; p-3 has dimensions: null and no variants at all; the second variant of p-1 is discontinued; p-3’s price has three decimal places.

The flow, docs/src/examples/catalog/ferry/flows/products.yaml, writes five tables. Taken a target at a time:

One row per record

flows:
  - name: products
    from: shop.products
    sink: warehouse
    to:
      products:
        key: [id]
        columns:
          id: _id
          name: name
          price: {expr: price, type: "decimal(10,2)", allow_lossy: true}
          price_cents: int(round(price * 100))
          tag_count: int(coalesce(cardinality(tags), 0))

A target with no parent: and no unnest: gets one row per record. Bare names are the document’s fields.

A split: another table from the same record

      # A split: a second table from the same document, one row per product
      # that has dimensions.
      product_dimensions:
        parent: products
        key: [product_id]
        where: dimensions != null
        columns:
          product_id: $parent.id
          width_cm: dimensions.width_cm
          height_cm: dimensions.height_cm

parent: products makes this a child of products: it is written after it, it sees the parent’s mapped row as $parent ($parent.id is the products.id column, already computed), and since it maps the parent’s whole key from $parent, it gets a foreign key to products. Without an unnest:, a child has one row per parent row, and its scope is still the document (dimensions.width_cm). Its where: keeps only products that have dimensions: p-3’s null and p-2’s missing field both fail it.

Unnest an array of scalars

      # An array of strings: `$item` is the element itself.
      product_tags:
        parent: products
        unnest: tags
        key: [product_id, position]
        columns:
          product_id: $parent.id
          position: $index
          tag: $item

unnest: tags writes one row per element of tags. $index is the element’s 0-based position and $item the element itself, for arrays of scalars. An empty array, a missing field and null all give no rows, and the parent row is still written.

Unnest an array of objects

      # An array of objects: bare names are the element's fields.
      variants:
        parent: products
        unnest: variants
        key: [product_id, position]
        where:
          on_sale: not coalesce(discontinued, false)
        columns:
          product_id: $parent.id
          position: $index
          sku: sku
          color: color
          stock: stock

When the elements are objects, bare names are the element’s fields (sku is short for $item.sku). The target’s where: drops the discontinued variant. discontinued is missing in most variants, and a missing value compares as null, so coalesce() gives it a default: a plain discontinued != true would drop every variant without the field.

Unnest inside an unnest

      # Nested: one row per option of each variant written above.
      variant_options:
        parent: variants
        unnest: options
        key: [product_id, variant_position, position]
        columns:
          id: $key
          product_id: $parent.product_id
          variant_position: $parent.position
          position: $index
          name: name
          value: value

The parent is variants, so unnest: options is resolved in the scope of each variant, and $parent is the mapped variant row. $index is always the innermost position. $key is a deterministic UUID for the row: version 5, over the driver record’s key, the unnest path with every outer index, and $index. The same document always gets the same keys, however it is partitioned and however often it is re-run, so $key is safe as a primary key for rows that have no natural one.

Because the discontinued variant was filtered out of variants, its options are dropped with it: a filter propagates to children through parent:.

Assertions

    expect:
      every_product: count(products) == source.count()
      every_tag: count(product_tags) == source.sum(coalesce(cardinality(tags), 0))

source.sum(...) adds up an expression over the documents the run wrote, as it read them; the target side is queried after the run (Verification).

The result

dataferry run, then the five tables, as psql shows them:

run 8f47986ff71c4ecf8d13b0b17d4c5d39
  flow `products`: TxnWithState
run 8f47986ff71c4ecf8d13b0b17d4c5d39: complete; 1 partitions; read 3, dead-lettered 0; wrote product_dimensions 1, product_tags 3, products 3, variant_options 2, variants 2
verification of `warehouse` passed; report: ./.ferry/runs/8f47986ff71c4ecf8d13b0b17d4c5d39/report.md
select * from products order by id;
 id  |   name    | price | price_cents | tag_count 
-----+-----------+-------+-------------+-----------
 p-1 | Desk lamp | 24.50 |        2450 |         2
 p-2 | Notebook  |  3.99 |         399 |         0
 p-3 | Cable     |  7.13 |         713 |         1
(3 rows)

select * from product_dimensions order by product_id;
 product_id | width_cm | height_cm 
------------+----------+-----------
 p-1        |       15 |        40
(1 row)

select * from product_tags order by product_id, position;
 product_id | position |   tag    
------------+----------+----------
 p-1        |        0 | lighting
 p-1        |        1 | office
 p-3        |        0 | office
(3 rows)

select * from variants order by product_id, position;
 product_id | position |   sku    | color | stock 
------------+----------+----------+-------+-------
 p-1        |        0 | LAMP-BLK | black |    12
 p-2        |        0 | NB-A5    | red   |   100
(2 rows)

select * from variant_options order by product_id, variant_position, position;
                  id                  | product_id | variant_position | position | name | value 
--------------------------------------+------------+------------------+----------+------+-------
 9594e786-b1ed-5fb8-894b-86c98f35905f | p-1        |                0 |        0 | bulb | LED
 b2749e44-412c-5285-8b63-6ba77dd8d21b | p-1        |                0 |        1 | plug | EU
(2 rows)

p-3’s price of 7.125 is 7.13 after narrowing to decimal(10,2), and 713 cents. p-2 is in products with no tags and no dimensions. The discontinued LAMP-WHT and its option are nowhere, and p-3, which has no variants field, simply has no variant rows.

The rules

Split

  • Each key under to: is a table. Write order follows parent:; tables with no relation are ordered by name.
  • A record is written in full or not at all: if computing any row of any target fails, none of the record’s rows are written.

Unnest

  • unnest: <path> is resolved in the scope of the target’s parent: the parent’s element if the parent is itself unnested, otherwise the driver record. A target with unnest: but no parent: unnests from the driver record and has no $parent.
  • Elements of an empty array, a missing field or null: none, and the parent is still written. This isn’t configurable.
  • A value that isn’t an array (tags: "x") is an error: the record is rejected. If the source can’t give a field one type (a MongoDB field that is an array in some documents and a string in others), it is read as json, and an unnest of it takes JSON arrays element by element and rejects anything else.
  • A null element still gives a row, with a null $item.
  • Paths use dots for nested fields (unnest: shipping.parcels). There is no indexing into an array (items[0]); items[*].sku in an expression is the list of every element’s sku.
  • $index, $item and $key exist only in unnest targets ($item only in one that unnests a path, not a many join), and $parent only in a target with parent:.

The golden example

The quickstart’s flow is a split and an unnest in one: orders from each document and order_items from its items array, with id: $key, order_id: $parent.id and line_no: $index:

      # Part of S2: the first end-to-end run writes both tables.
      order_items:
        parent: orders
        unnest: items                       # empty (…0002) or missing (…0003): no rows
        key: [order_id, line_no]
        columns:
          id: $key                          # UUIDv5 over (_id, items, $index)
          order_id: $parent.id              # the mapped parent row
          line_no: $index
          sku: sku
          qty: int(qty)                     # "three" (…0006) rejects the whole document
          unit_cents: int(round(price * 100))