Assemble documents with nest

Use nest when an application needs one current document but the source data is split across related tables, including tables in different database engines. The transform builds the document during the initial load and updates it as either source emits later changes.

This guide uses a MySQL orders root table and a PostgreSQL shipments child table. It assumes the Quickstart or another non-production server is running, the MySQL, PostgreSQL, and MongoDB connector artifacts are registered, and both source connections pass a server-side test.

1. Choose the document root

Start with the entity that should produce one output document. In this example, each orders.id becomes one materialized document. Every shipment carries the parent identity in shipments.order_id:

orders.id  1 ─────< shipments.order_id  1

nest follows parent identity. It is not a general lookup or SQL join. If a child row does not carry the key that points to its parent, redesign the source shape or choose a different document root.

2. Select the source tables

Create one CDC-capable source connection for each database engine:

version: tapstate/v1
kind: source
id: orders_db
connector: mysql
config:
  host: mysql.internal
  port: "3306"
  database: shop
  username: ${MYSQL_USER}
  password: ${MYSQL_PASSWORD}
mode: cdc
tables: [orders]

The PostgreSQL source uses its own field names and selects the shipment table:

version: tapstate/v1
kind: source
id: fulfillment_db
connector: postgres
config:
  host: postgres.internal
  port: "5432"
  database: fulfillment
  schema: public
  user: ${POSTGRES_READER_USER}
  password: ${POSTGRES_READER_PASSWORD}
  logPluginName: pgoutput
mode: cdc
tables: [shipments]

Keep credentials out of committed files. See the MySQL and PostgreSQL guides for permissions and connection fields. For a PostgreSQL child table that can be updated or deleted, configure REPLICA IDENTITY FULL before starting CDC.

3. Define the document tree

Map short aliases to the source table streams, choose the root key, and describe where each child belongs:

version: tapstate/v1
kind: pipeline
id: order_pipeline
source: [orders_db, fulfillment_db]
settings:
  read_mode: snapshot_and_cdc
transforms:
  - id: assemble
    type: nest
    from:
      order: orders
      shipment: shipments
    root:
      from: order
      key: [id]
      embed:
        - from: shipment
          on:
            order_id: id
          as: array
          path: shipments
          arrayKey: [id]
view:
  id: order_state
  from: assemble
  primary_key: id

Here, on means shipments.order_id must equal orders.id. as: array creates shipments: [] when an order has no children. Use as: object only when the relationship has at most one child.

The view materializes order_state in the preview's managed store. No serve block or MongoDB target connection is required for this path.

4. Discover both source schemas, then apply the pipeline

Validate locally, apply each source, discover its schema, and then apply the complete workspace before starting the pipeline:

tapstate validate --workdir tapstate-work
tapstate -w tapstate-work
connect http://127.0.0.1:8080
login admin
apply source/orders_db.tap.yml
apply source/fulfillment_db.tap.yml
discover-schema orders_db
discover-schema fulfillment_db
apply
start order_pipeline
status order_pipeline

The first two apply commands store the connections before discovery. The final apply stores the pipeline after discovery has supplied both sources' field types. Expressions that read after.<field> or before.<field> follow the same order.

5. Verify initial state and CDC

Do not treat an accepted start command as proof that data moved. Verify all of the following:

1. status order_pipeline remains running.
2. logs order_pipeline has no unresolved coded failure.
3. metrics order_pipeline shows processed records and no unexplained nestDeadLettered.<namespace> changes.
4. The materialized order_state contains one document per order and the expected number of embedded shipments.
5. A PostgreSQL shipment insert, update, and delete updates the expected MySQL order document. A MySQL order update keeps its embedded shipments.

A nest writes the assembled document as a unit. Include a document near the expected maximum size in capacity and latency tests; a backing state layer cannot make one oversized document fit.

For whole-document delivery behavior, peak-change planning, and a practical test matrix, see Plan nest capacity and delivery behavior.

Current limits

• Every nested relationship must point from child to parent.
• Sibling branches at the same level must agree on the parent identity.
• nest does not perform arbitrary lookups; the separately declared join transform remains refused by the current runtime.
• The current preview is single-node and does not provide production recovery guarantees.

Continue with Observe a pipeline and the nest reference for runtime signals and every field. Use Handle structural key changes with nest and Plan nest capacity and delivery behavior for the operational boundaries that apply to the example.