Skip to main content
Beta Available for BigQuery projects. On Lightdash Cloud, ask Lightdash support to enable it for your organization. On self-hosted deployments, add unnest-repeated-columns to LIGHTDASH_ENABLE_FEATURE_FLAGS; see Feature flags. What Beta means.
Columns can store records and arrays. Lightdash turns their contents into fields, so users can explore them without writing UNNEST queries.

​
Supported types

For records, add each leaf you want to expose. For an array of scalar values, add the array column itself.

​
From your warehouse to Lightdash

Imagine an orders table with one row per order. Add the full path of each field you want to expose to your dbt YAML. Lightdash reads the type and structure from your warehouse, so you do not need to describe the nesting in YAML.
In your warehouse
Orders table with a nested customer record, repeated line item records, and repeated scalar tags, each with an array offset
In your semantic layer
After you deploy the project, users see:
  • City on the Orders table. Because customer is not repeated, the field stays at the order grain.
  • Sku, Price, and Offset under Orders: Line items. Each line item is one row, and offset is its zero-based position in the array.
  • Value and Offset under Orders: Tags. value contains the tag itself.
Only the fields listed in YAML are exposed. For example, customer.name remains hidden until you add it.

​
How queries behave

Lightdash expands a repeated column only when a query selects, filters, or sorts by one of its fields. Each array element adds a row to the query before Lightdash groups the results. Orders with an empty or NULL array remain in the result with NULL repeated fields. The primary_key lets Lightdash deduplicate metrics defined on the parent model when repeated fields change the query grain. Metrics defined on a repeated field are calculated at the array-element grain. A filter on a repeated field keeps the matching elements, not every element from each matching order. For example, filtering to one SKU returns that SKU rather than all line items from orders that contain it.

​
Query warnings

Selecting fields from two separate repeated columns pairs every element from one array with every element from the other. This can inflate metrics. For example, selecting both line_items.sku and a field from a repeated shipments column multiplies the line items by the shipments for each order. Lightdash shows a warning when a query combines repeated columns this way, or when a metric on one repeated column could be inflated by another.

​
Deploy your changes

Before running lightdash deploy, update the Lightdash CLI. The CLI reads the nested-column setting from the Lightdash server when it compiles your Explores. lightdash generate does not add repeated columns or their leaves to YAML. Add those entries manually after generating your model configuration.

​
Limitations

  • Record and array containers cannot be selected directly as fields.
  • To access an array element by index, such as line_items[0].sku, define a dimension with custom sql.
  • SQL Runner and virtual views recognize nested column types but do not expand them into fields.