Skip to main content

Nested datasets

A nested dataset unpacks a nested field of a dataset's rows into a dataset of its own: order lines inside orders, leads.data into leads_data. It is a dataset like any other, exported to SQL Server or Fabric as the parent is and read by the Data API. It reads the parent's loaded rows, so nothing is fetched from the source twice; it loads right after the parent, inside the parent's job; and every row carries the parent id as _parentId. Nested datasets can themselves have nested datasets.

Create one​

Open the parent's Edit Dataset dialog and the Nested datasets tab (shown once the loaded rows hold an object or a list, or once something has been derived). It lists the datasets already derived with their nested field and key, and under Derive another shows the parent's loaded rows in the picker:

  1. Mark the nested list or object with the crosshair.
  2. Click the fields inside it that identify a row within its parent, for example a line number. Pick none to key rows by their position.
  3. Accept or change the Dataset name, proposed as <parent>_<field>.
  4. Leave Run now ticked to queue the first full load, and press Create nested dataset.

The key is always _parentId && <picked field> or _parentId && _index, so a row's id is <parent id>-<key>; positions count from 1. The new dataset appears in the dataset lists with a from <parent> chip, and a relationship from _parentId to the parent's _id is recorded for the Data API.

How it loads​

  • A full load reads every parent row of the instance and afterwards marks the rows it did not produce as deleted.
  • An incremental load reads the parent rows changed since the previous nested load started, and marks deleted the rows of changed or deleted parents that were not produced again. It falls back to a full load when there is no previous load or the change set is too large.
  • Nested datasets load right after their parent, in the parent's job, each with its own load log; a failure makes the parent's load a warning. A source-system Refresh now never queues them directly, but a refresh of the nested dataset itself works, and the parent is never loaded under a running nested load.
  • If no parent row has the nested field, the load fails rather than emptying the table.

What is different about a nested dataset​

  • It takes no endpoints and has no webhook; its edit dialog labels the fields Nested field and Key within the parent, and hides Pre-load delete and _url. Changing the nested field on a loaded dataset calls for a full load.
  • The parent cannot be deleted while it has nested datasets.
  • A calculated field on it can use the parent row's fields and _parentId.
  • The parent's progressive-load setting applies to it.
  • A rename of a field in the parent does not rewrite the nested field path or key; update them by hand.