Skip to main content

Mastering nested and repeated fields in LookML

· 18 min read

Working with nested STRUCT and repeated ARRAY data types in modern cloud data warehouses like BigQuery, Snowflake, and Databricks is standard practice for performance and storage efficiency. However, BI tools often struggle with semi-structured data, forcing engineers to build complex ETL pipelines that flatten everything into massive, slow tables.

Think of nested data like a modular bento box: instead of scattering your main course, side dish, and sauce across separate plates in different rooms, everything for a single order is packaged neatly in one self-contained box.

When a standard SQL developer sees a bento box, their first instinct is to dump all the boxes onto a conveyer belt and unroll every item flat. Before you spin up a dbt model or ETL pipeline to flatten your nested data, realize that Looker handles STRUCT and ARRAY fields natively inside LookML—preserving columnar storage efficiency while generating clean SQL on demand.

Looker out of the box​

When you generate LookML from a BigQuery schema containing nested and repeated data, Looker handles the initial modeling automatically across different nesting depths:

  • Single 1:1 STRUCT fields (like geo.country) become dimensions that reference the leaf field directly using dot notation (sql: ${TABLE}.geo.country ;;).
  • Repeated 1:N ARRAY<STRUCT> fields (records in arrays) trigger Looker to hide the raw array column in the parent view (hidden: yes), generate a dedicated child view for the array elements, and build a LEFT JOIN UNNEST(${parent.array}) Explore join with relationship: one_to_many.
  • Structs inside arrays (such as items.item_dimensions.width) are parsed as dot-notation dimensions directly inside the unnested array view (sql: ${TABLE}.item_dimensions.width ;;).
  • Arrays inside arrays (such as items.item_params) generate chained child views and cascading unnest joins in the Explore, unnesting the deeper array relative to the first-level view (LEFT JOIN UNNEST(${items.item_params})).

Here is how nested fields work in Looker, how symmetric aggregates protect you from fanout bugs, and how to model deeply nested structures cleanly.

The problem: semi-structured data meets traditional BI​

Cloud data warehouses prefer nested and repeated structures because they preserve data locality and cut disk I/O. In Google BigQuery, accessing a single subfield inside a STRUCT reads only that specific leaf column—giving you maximum column-pruning performance without physical table joins.

As BigQuery performance guidelines emphasize, keeping data in ARRAY<STRUCT> format localizes child records to individual processing slots. Pre-flattening relational data forces expensive network data shuffling across slots for GROUP BY operations; keeping arrays nested allows BigQuery to execute queries in parallel at full speed without shuffle overhead.

However, repeated fields (ARRAY<STRUCT>) represent a 1:N relationship embedded directly inside a single row. If a naive SQL query un-nests an array, the parent record is multiplied for every item in that array.

+------------+--------------------+----------------+
| event_id   | item_name (array)  | event_revenue  |  <-- Unnested row explosion!
+------------+--------------------+----------------+
| evt_101    | Ergonomic Mouse    | $150           |
| evt_101    | Mechanical Keyboard| $150           |  <-- Double counted revenue!
+------------+--------------------+----------------+

Without special handling, any SUM(event_revenue) across this unnested dataset produces a fanout bug, double-counting revenue for multi-item events. Looker resolves this without requiring pre-flattened data tables.

LookML views are virtual wrappers, not database objects​

When Looker suggests creating a new view for every nested record or repeated array, engineers coming from traditional SQL backgrounds often worry that creating dozens of views will clutter their database, slow down queries, or bloat their schema.

LookML views do not work that way.

A LookML view is not a physical database view (CREATE VIEW ...). It executes zero DDL statements, creates zero database objects, and consumes zero storage or memory in BigQuery. It is strictly a virtual metadata wrapper in LookML code mapping SQL column references to user-facing dimensions and measures.

Creating dedicated LookML views for repeated arrays provides several architectural advantages:

  • Item-level measures like total_item_revenue or average_unit_price live inside the ga4_event_items view, preventing users from confusing item-grain metrics with event-grain metrics (total_event_revenue).
  • Once defined, the ga4_event_items view can be joined into multiple Explores (events, user_conversions, order_fulfillment) without duplicating LookML code.
  • Defining a view in an Explore join costs nothing at query time. If a user runs a query selecting only top-level fields, Looker ignores the unnested view entirely and omits the LEFT JOIN UNNEST(...) clause from the generated SQL.
  • You can apply custom view_label titles, field-level security, or field groupings to the unnested array independently of the parent record.

Alternatives for downstream pre-flattening needs​

If your architecture still requires flattened datasets or pre-aggregated tables for downstream tools, external reporting, or specialized analytics, avoid building separate, un-governed ETL pipelines. Instead, use Looker's native features so downstream systems can take full advantage of Looker's semantic modeling and security:

  • Expose governed Looker Explores directly to third-party tools using Looker BI Connectors like the Looker Studio connector or Tableau connector. External applications query your LookML semantic layer directly without requiring pre-flattened tables in your warehouse.
  • Define pre-joined or pre-flattened datasets inside LookML using native derived tables. Setting publish_as_db_view instructs Looker to publish the PDT as a stable, physical view in your underlying database scratch schema. External SQL scripts and ETL tools can then query SELECT * FROM scratch_schema.pdt_view directly in BigQuery while inheriting Looker's unnesting logic and datagroup build schedules.
  • Implement aggregate awareness roll-up tables (aggregate_table). Aggregate awareness accelerates access to deeply nested and fanned-out data by pre-aggregating common query patterns into summary tables. Looker automatically routes downstream queries to these optimized roll-ups when available while preserving drill-down access to the underlying unnested arrays.

Public dataset blueprint: GA4 e-commerce events​

This guide uses the public Google Analytics 4 dataset in BigQuery (bigquery-public-data.ga4_obfuscated_sample_ecommerce.events) to demonstrate these patterns.

This schema contains three distinct tiers of nesting:

  1. Single 1:1 nested structs like geo containing continent, country, and city.
  2. Repeated 1:N arrays of structs like items containing item_id, item_name, price, and quantity.
  3. Deeply nested 1:N:M arrays within arrays like items.item_params containing custom key-value pairs per item.

Single structs: instant access via dot notation​

For non-repeated STRUCT fields, you do not need joins or un-nesting. Access subfields directly in LookML dimension definitions using SQL dot notation.

view: ga4_events {
  sql_table_name: `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` ;;

  dimension: event_id {
    primary_key: yes
    type: string
    sql: CONCAT(${TABLE}.event_date, '_', ${TABLE}.event_timestamp, '_', ${TABLE}.user_pseudo_id) ;;
  }

  dimension: event_name {
    type: string
    sql: ${TABLE}.event_name ;;
  }

  # Direct dot-notation for 1:1 STRUCT fields
  dimension: country {
    group_label: "Geography"
    type: string
    sql: ${TABLE}.geo.country ;;
  }

  dimension: city {
    group_label: "Geography"
    type: string
    sql: ${TABLE}.geo.city ;;
  }
}
Column pruning advantage

When a user selects only country and event_name in Looker, BigQuery reads only those two specific leaf columns from disk. It never reads the rest of the geo record or any unreferenced fields.

Repeated arrays: dynamic unnesting in Explores​

When dealing with repeated arrays (ARRAY<STRUCT>), create a separate LookML view for the nested entity and join it in your Explore using BigQuery's UNNEST syntax.

Create the nested item view​

For symmetric aggregates to accurately compute measures on unnested arrays, every view must have a primary key. When nested array items lack a unique natural ID, use BigQuery's WITH OFFSET clause during the unnest join to expose the array row index (0, 1, 2...), then concatenate it with the parent ID to form a surrogate primary key.

view: ga4_event_items {
  # No sql_table_name needed. This view represents the unnested array.

  dimension: item_offset {
    hidden: yes
    type: number
    sql: item_offset ;;
  }

  dimension: primary_key {
    primary_key: yes
    hidden: yes
    type: string
    sql: CONCAT(${ga4_events.event_id}, '_', CAST(${item_offset} AS STRING)) ;;
  }

  dimension: item_id {
    type: string
    sql: ${TABLE}.item_id ;;
  }

  dimension: item_name {
    type: string
    sql: ${TABLE}.item_name ;;
  }

  dimension: item_category {
    type: string
    sql: ${TABLE}.item_category ;;
  }

  dimension: price {
    type: number
    value_format_name: usd
    sql: ${TABLE}.price ;;
  }

  dimension: quantity {
    type: number
    sql: ${TABLE}.quantity ;;
  }

  measure: total_quantity {
    type: sum
    sql: ${quantity} ;;
  }

  measure: total_item_revenue {
    type: sum
    sql: ${price} * ${quantity} ;;
    value_format_name: usd
  }
}

Join using UNNEST in the Explore​

In your model file, join the nested view to the main Explore using LEFT JOIN UNNEST(...) with WITH OFFSET, and explicitly set relationship: one_to_many.

explore: ga4_events {
  label: "GA4 E-Commerce Events"

  join: items {
    from: ga4_event_items
    sql: LEFT JOIN UNNEST(${ga4_events.items}) AS items WITH OFFSET AS item_offset ;;
    relationship: one_to_many
  }
}

Unnest simple scalar arrays (ARRAY<STRING> or ARRAY<INT64>)​

If your nested array contains primitive values like ARRAY<STRING> tags or SKUs rather than structs, unnest it using the same UNNEST join pattern. Reference the unnested scalar value in your dimension using ${TABLE}:

# Explore join for a simple string array: ARRAY<STRING>
explore: impressions {
  join: unresolved_skus {
    sql: LEFT JOIN UNNEST(${impressions.unresolved_skus}) AS unresolved_skus ;;
    relationship: one_to_many
  }
}

view: unresolved_skus {
  dimension: sku {
    type: string
    sql: ${TABLE} ;; # Directly references the scalar array element
  }
}

Alternative pattern: inline subqueries for key-value arrays​

When working with key-value repeated structs (like GA4 event_params or event log properties), you may only want to extract 1 or 2 specific property values directly onto the parent event record without exposing a full unnested view in the Explore.

Instead of an Explore-level UNNEST join, use an inline scalar subquery directly inside a LookML dimension:

view: ga4_events {
  # Extract a single key's value directly as a parent-level dimension
  dimension: session_id {
    group_label: "Session Attributes"
    type: string
    sql: (
      SELECT value.string_value
      FROM UNNEST(${TABLE}.event_params) AS param
      WHERE param.key = 'ga_session_id'
      LIMIT 1
    ) ;;
  }
}
Choosing between Explore joins and inline subqueries
  • Use inline subqueries when you need 1–2 specific key values as first-class parent attributes without exposing a 1:N unnested view in the Explore UI or risking row fanout.
  • Use Explore UNNEST joins when users need to slice, filter, or aggregate across arbitrary keys and values dynamically in the Explore UI.

Filter nested arrays without row explosion (WHERE EXISTS in LookML)​

Unnesting arrays in SQL expands parent rows into 1:N rows. If your goal is only to filter parent records based on nested array conditions (for example, finding events where the purchased items array contains a specific category), unnesting in the Explore join adds unnecessary processing.

Instead, create a LookML filter using a BigQuery EXISTS subquery combined with Liquid:

view: ga4_events {
  filter: has_purchased_category {
    group_label: "Array Filters"
    type: string
    sql: EXISTS (
      SELECT 1
      FROM UNNEST(${TABLE}.items) AS item
      WHERE {% condition has_purchased_category %} item.item_category {% endcondition %}
    ) ;;
  }
}

When a user applies has_purchased_category = "Apparel" in the Explore filter bar, BigQuery evaluates the condition natively inside EXISTS (...). The parent table row count remains 1:1, avoiding row multiplication while preserving single-table query speed.

Fix filter suggestions on unnested views (full_suggestions: yes)​

When users filter on dimensions inside an unnested view (like item_name in ga4_event_items), Looker's default auto-suggestion engine queries the leaf view standalone:

-- Default Looker suggestion query (Fails for unnested views)
SELECT DISTINCT item_name
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.ga4_event_items`
LIMIT 1000

Because ga4_event_items is a virtual LookML wrapper around an unnested array rather than a physical database table, this standalone query fails, breaking auto-complete suggestions in the Explore filter bar.

To fix this, add full_suggestions: yes to any unnested dimension used as a filter:

view: ga4_event_items {
  dimension: item_name {
    type: string
    sql: ${TABLE}.item_name ;;
    full_suggestions: yes # Forces Looker to use the full Explore UNNEST query for suggestions
  }
}

With full_suggestions: yes, Looker includes the entire Explore query structure—including FROM events LEFT JOIN UNNEST(events.items)—when populating drop-down suggestions.

Optimizing suggestion queries on heavy arrays

If unnesting massive tables to scan for distinct filter values slows down UI loading or raises warehouse costs, consider these options:

  • Hardcode static values with suggestions: ["Value A", "Value B"] to bypass database queries entirely.
  • Route suggestion queries to a smaller lookup dimension in a pre-aggregated reference Explore using suggest_dimension and suggest_explore.
  • Set suggest_persist_for: "24 hours" to cache generated suggestions longer and cut warehouse query volume.
  • Use aggregate tables matching your filter dimension to serve suggestions from pre-aggregated rollups (see Accelerate Suggestions with Aggregate Awareness).

Fanout protection with symmetric aggregates​

What happens when a business user selects country from the parent event view, item_name from the items view, and total_event_count from the parent view?

Looker's Symmetric Aggregates engine automatically detects the one_to_many relationship and primary key. Instead of generating a naive COUNT(*) or SUM(revenue) that would multiply parent rows, Looker rewrites the SQL using primary-key hashing:

-- Generated BigQuery SQL from Looker
SELECT
  ga4_events.geo.country AS ga4_events_country,
  items.item_name AS items_item_name,
  -- Symmetric Aggregate prevents fanout double-counting:
  COUNT(DISTINCT CASE WHEN ga4_events.event_id IS NOT NULL THEN ga4_events.event_id ELSE NULL END) AS ga4_events_event_count,
  COALESCE(CAST(SUM(DISTINCT (CAST(FLOOR(COALESCE(items.price, 0) * (1000000*1.0)) AS NUMERIC) +
    (CAST(FARM_FINGERPRINT(CAST(ga4_events.event_id AS STRING)) AS NUMERIC) / 4611686018427387904.0)) / (1000000*1.0))
    - SUM(DISTINCT (CAST(FARM_FINGERPRINT(CAST(ga4_events.event_id AS STRING)) AS NUMERIC) / 4611686018427387904.0)) AS NUMERIC), 0) AS items_total_item_revenue
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` AS ga4_events
LEFT JOIN UNNEST(ga4_events.items) AS items
GROUP BY 1, 2

Symmetric aggregates guarantee that parent measures remain accurate regardless of how many arrays you un-nest.

Under the hood: efficient SQL generation and on-demand joins​

BigQuery physically co-locates nested records on disk. Looker complements this architecture through on-demand SQL generation: query-time unnesting logic is defined once in LookML, but executed only when needed.

If a dashboard tile queries only top-level fields (like event_name or country), Looker's SQL generator completely omits the LEFT JOIN UNNEST(...) clause.

-- Looker generates this clean SQL when no nested item fields are selected:
SELECT
  ga4_events.geo.country AS ga4_events_country,
  COUNT(*) AS ga4_events_event_count
FROM `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_*` AS ga4_events
GROUP BY 1

Because Looker does not execute UNNEST unless a nested field is requested, you incur zero query performance penalty for modeling deeply nested arrays in your Explore.

Design for Explore viewability and readability​

Raw semi-structured schemas can quickly overwhelm business users if exposed as unformatted JSON blobs. Follow these LookML UX principles to keep your Explores clean:

  • Mark the raw parent array field as hidden: yes in the main view so users interact only with unnested dimensions.
  • Organize 1:1 struct subfields under intuitive categories using group_label in the field picker.
  • Use view_label in the Explore join to give the unnested array a clean display name, such as view_label: "Purchased Items".

Handle deeply nested fields (arrays inside arrays)​

What if your data contains an array inside an array? In GA4, each item in items can contain its own nested array of parameters (items.item_params).

You can handle arbitrary nesting depths by chaining UNNEST joins in LookML:

view: ga4_item_params {
  dimension: param_key {
    type: string
    sql: ${TABLE}.key ;;
  }

  dimension: param_value {
    type: string
    sql: COALESCE(${TABLE}.value.string_value, CAST(${TABLE}.value.int_value AS STRING)) ;;
  }
}

# In your Explore definition:
explore: ga4_events {
  join: items {
    from: ga4_event_items
    sql: LEFT JOIN UNNEST(${ga4_events.items}) AS items ;;
    relationship: one_to_many
  }

  join: item_params {
    from: ga4_item_params
    sql: LEFT JOIN UNNEST(${items.item_params}) AS item_params ;;
    relationship: one_to_many
  }
}
Cartesian product warning

Avoid unnesting two independent top-level arrays (such as UNNEST(event_params) and UNNEST(items)) in the same query unless necessary. Parallel un-nesting produces a cross-join Cartesian product in BigQuery. If users need to query multiple independent arrays simultaneously, model them in separate Explores or use a Persistent Derived Table (PDT) to pre-aggregate.

Try it now: public BigQuery datasets with nested schemas​

If you want to practice modeling nested and repeated data in LookML without setting up your own database, Google Cloud provides several free public datasets in BigQuery:

  • bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_* features 3-tier nesting. Top-level geo and device structs map 1:1, the items array maps 1:N purchased products, and items.item_params contains a nested array of key-value parameters inside each item.
  • bigquery-public-data.github_repos.commits contains 2-tier nesting for open-source code activity. Commit author and committer details exist as 1:1 structs, while difference contains an array of structs tracking file modifications (old_path, new_path, old_sha1, new_sha1).
  • bigquery-public-data.fhir_synthea.patient represents healthcare HL7 FHIR records with deep clinical nesting. The communication array contains a language struct, which in turn contains a coding array of code and display structs (communication.language.coding).

You can point a Looker connection to bigquery-public-data and generate LookML directly from these tables to inspect how Looker automatically builds dot-notation dimensions and unnested child views.

Governance and lineage in Knowledge Catalog​

Modeling nested fields cleanly in LookML does more than produce fast SQL—it integrates directly with enterprise data governance tools like Knowledge Catalog.

Looker's native integration periodically ingests LookML metadata, view definitions, Explores, and dashboards into Knowledge Catalog asset records:

  • Knowledge Catalog automatically maps downstream Looker dimensions (like ${TABLE}.geo.country or ${TABLE}.price) back to their exact BigQuery source columns, including nested STRUCT leaf nodes.
  • Descriptions added in LookML (description: "The item unit price in USD") sync automatically to Knowledge Catalog, creating a single source of truth for both raw database schemas and semantic BI layers.
  • If a data engineer changes a nested field schema in BigQuery, Knowledge Catalog uses data lineage tracking to pinpoint every downstream dashboards and explores impacted by the change.

Auto-generating LookML for derived tables​

Looker generates native LookML for physical BigQuery tables with STRUCT and ARRAY columns, but fails when prototyping custom queries: SQL Runner and Model Runner cannot parse or generate nested LookML from custom SQL queries or derived tables.

When you run a custom query projecting nested structs or arrays in SQL Runner, the derived table LookML flattens or ignores the record hierarchy. It omits dot-notation dimensions for leaf STRUCT attributes and skips generating dedicated child views and chained LEFT JOIN UNNEST(...) definitions for repeated ARRAY fields.

Workaround: scratch-schema view generator utility​

To generate LookML from a complex query without creating permanent views in production BigQuery, automate the exact table workflow Looker expects.

Using lkr-dev-cli in Code Mode (lkr code-mode), run a script inside an isolated sandbox to handle the full lifecycle; the script will:

  1. Verify that the target connection configures a scratch database.
  2. Wrap your raw SQL query in a temporary view statement (CREATE OR REPLACE VIEW <tmp_db_name>.<view_name> AS ...) and run it using Looker SQL Runner endpoints (create_sql_query + run_sql_query).
  3. Call generate_lookml_with_new_files() against the temporary view. Because BigQuery exposes table schema metadata for views, Looker generates dot-notation dimensions and parent-child UNNEST joins automatically.
  4. Drop the temporary view (DROP VIEW IF EXISTS) from the scratch schema so no temporary objects persist in BigQuery.
important

The scratch schema for your connection must be set and accessible by the Looker service account or user who is performing the generation.

Script and sample query resources​

Download the generator utility and multi-level sample query to run on your instance:

  • bq_view_lookml_generator.py — The Python script for Looker Code Mode (lkr code-mode) that orchestrates creating the scratch view, invoking generate_lookml_with_new_files(), and deleting the view.
  • nested_structs_sample.sql — Sample BigQuery SQL constructing multi-level STRUCT and ARRAY attributes (customer, metadata, items, and item-level attributes).
Functional overview​

The bq_view_lookml_generator.py script executes inside the isolated Python sandbox with Looker SDK primitives pre-authenticated:

  1. Query all_connections() to locate the target database connection and verify that a tmp_db_name scratch schema is configured.
  2. Run a CREATE OR REPLACE VIEW <tmp_db_name>.<custom_view_name> statement through Looker's create_sql_query and run_sql_query endpoints.
  3. Call generate_lookml_with_new_files() against the temporary view so Looker's table inspector builds child view files and cascading unnest joins.
  4. Run DROP VIEW IF EXISTS over the scratch view so no temporary database objects linger in BigQuery.

Running the generator script​

Execute the script via uvx without installing manual SDK dependencies. Provide either --var connection=... or --var model=... to target your database connection or model:

uvx --from "lkr-dev-cli[codemode]" lkr \
  --client-id YOUR_LOOKER_CLIENT_ID \
  --client-secret YOUR_LOOKER_CLIENT_SECRET \
  --base-url https://looker.company.com \
  --dev code-mode sandbox \
  --file=bq_view_lookml_generator.py \
  --var project=my_looker_project \
  --var connection=my_bigquery_connection \
  --var view_name=order_nested_example \
  --var sql_file=nested_structs_sample.sql

Generated LookML output​

The script produces view definitions and a hidden base Explore with chained unnest joins:

explore: orders_nested_view {
  hidden: yes
  join: orders_nested_view__items {
    view_label: "Orders Nested View: Items"
    sql: LEFT JOIN UNNEST(${orders_nested_view.items}) as orders_nested_view__items ;;
    relationship: one_to_many
  }
  join: orders_nested_view__items__attributes {
    view_label: "Orders Nested View: Items Attributes"
    sql: LEFT JOIN UNNEST(${orders_nested_view__items.attributes}) as orders_nested_view__items__attributes ;;
    relationship: one_to_many
  }
}

view: orders_nested_view {
  # ... rest of view definition
}
view: orders_nested_view__items {
  # ... rest of view definition
}
view: orders_nested_view__items__attributes {
  # ... rest of view definition
}

Extending the hidden base Explore​

To reuse and build upon the hidden base explore across models, refer to reusing code with extends:

explore: order_items {
  extends: [orders_nested_view]
  join: orders_nested_view {
    sql_on: ${orders_nested_view.order_id} = ${orders_nested_view.order_id} ;;
  }
  # More joins...
}
Extending Explore caveat

When extending a base explore, all joins and unnests defined in the base explore (orders_nested_view) are automatically inherited and brought into any extending explore (order_items).

Summary​

By using LookML's native support for STRUCT dot notation and UNNEST joins:

  • Eliminate fragile ETL pipelines designed solely to flatten semi-structured data.
  • Preserve BigQuery column-pruning for maximum performance and lower costs.
  • Prevent metric double-counting via automatic Symmetric Aggregates.
  • Maintain full end-to-end data lineage in Knowledge Catalog.