Vistelio | Modeling Nested and JSON Data in LookML: ARRAY, STRUCT, and BigQuery's JSON Type

Most warehouses that feed Looker today are not neat star schemas. Event pipelines, product analytics exports, Firebase and GA4 tables, webhook payloads, and anything coming out of a streaming ingestion job all land in BigQuery as repeated records or as a single JSON column. Analysts ask for "just one field out of that blob", a developer writes a one-off derived table, and six months later the project has nine different ways of reading the same nested payload.

This tutorial covers the patterns we use to model semi-structured BigQuery data in LookML: flattening with UNNEST, modeling repeated records as their own views, reading BigQuery's native JSON type, and — the part most teams get wrong — keeping fanout and query cost under control once the nesting is exposed.

Three shapes, three strategies

Before writing any LookML, classify the column:

STRUCT (a single nested record). user.address.city. No row multiplication. Cheap. Model as plain dimensions.
ARRAY / repeated field (ARRAY<STRUCT<...>>). order.items[], event.params[]. Reading it multiplies rows. Model deliberately.
Native JSON column. Schemaless payloads. No row multiplication until you unnest an array inside it, but every field read costs a parse.

The strategy differs for each. Treating all three as "nested data" and reaching for one derived table is how explores get slow.

1. STRUCT columns: just dimensions

A STRUCT is the easy case. BigQuery lets you address it with dot notation, and LookML does not care that the column is nested:

view: users {
  sql_table_name: analytics.users ;;

  dimension: id {
    primary_key: yes
    type: number
    sql: ${TABLE}.id ;;
  }

  dimension: city {
    group_label: "Address"
    type: string
    sql: ${TABLE}.address.city ;;
  }

  dimension: country {
    group_label: "Address"
    map_layer_name: countries
    type: string
    sql: ${TABLE}.address.country ;;
  }
}

Two habits worth adopting here:

Use group_label to reproduce the nesting in the field picker. Users who know the source schema find fields faster, and an LLM reading your model gets the same structural hint.
Never expose the raw struct itself as a dimension. type: string on ${TABLE}.address renders as unreadable JSON and is useless in a filter.

2. Repeated fields: pick where the fanout happens

Repeated fields are where the decisions are. SELECT * FROM orders, UNNEST(items) AS item produces one row per item, not one row per order. That is exactly the join fanout problem, imported into a single table.

You have three options, in increasing order of correctness.

Option A: unnest in `sql_table_name` (fast, and usually wrong)

view: order_items_flat {
  sql_table_name: (
    SELECT o.order_id, o.created_at, item.sku, item.price
    FROM analytics.orders AS o, UNNEST(o.items) AS item
  ) ;;
}

This works and it is the pattern most teams start with. The problem is that the view's grain is now item, while everything named order_ in it is duplicated per item. Any type: sum on order_total in this view is inflated and Looker cannot protect you, because there is no join for symmetric aggregates to act on. Use this only when the view is genuinely item-grain and you delete or hide the order-level measures.

Option B: two views, one join (the default answer)

Keep the parent at parent grain and model the array as a child view. Give each a primary key, and join them with an explicit relationship so symmetric aggregates work:

view: orders {
  sql_table_name: analytics.orders ;;

  dimension: order_id {
    primary_key: yes
    type: number
    sql: ${TABLE}.order_id ;;
  }

  measure: total_revenue {
    type: sum
    sql: ${TABLE}.order_total ;;
    value_format_name: usd
  }
}

view: order_items {
  derived_table: {
    sql:
      SELECT
        o.order_id,
        item.sku            AS sku,
        item.price          AS price,
        item.quantity       AS quantity,
        ROW_NUMBER() OVER (PARTITION BY o.order_id ORDER BY item.sku) AS item_index
      FROM analytics.orders AS o,
      UNNEST(o.items) AS item ;;
    datagroup_trigger: nightly_etl
  }

  dimension: item_pk {
    primary_key: yes
    hidden: yes
    type: string
    sql: CONCAT(CAST(${TABLE}.order_id AS STRING), '-', CAST(${TABLE}.item_index AS STRING)) ;;
  }

  dimension: order_id { type: number hidden: yes sql: ${TABLE}.order_id ;; }
  dimension: sku      { type: string sql: ${TABLE}.sku ;; }

  measure: item_revenue {
    type: sum
    sql: ${TABLE}.price * ${TABLE}.quantity ;;
    value_format_name: usd
  }
}

explore: orders {
  join: order_items {
    type: left_outer
    relationship: one_to_many
    sql_on: ${orders.order_id} = ${order_items.order_id} ;;
  }
}

The synthetic primary key matters. Arrays rarely carry a natural unique key per element, and without a verified primary_key Looker's symmetric aggregates cannot de-duplicate orders.total_revenue when items fan out. ROW_NUMBER() over a deterministic ordering is the cheapest way to manufacture one.

Use datagroup_trigger (or persist_for) so the unnest is materialised once per ETL cycle rather than on every dashboard tile.

Option C: pre-aggregate the array (fastest, when you only need totals)

If the business only ever asks "how many items" and "how much item revenue", do not expose item grain at all. Collapse the array inside the parent view and skip the join entirely:

view: orders {
  sql_table_name: analytics.orders ;;

  dimension: item_count {
    type: number
    sql: ARRAY_LENGTH(${TABLE}.items) ;;
  }

  dimension: items_revenue {
    type: number
    sql: (SELECT SUM(i.price * i.quantity) FROM UNNEST(${TABLE}.items) AS i) ;;
  }

  measure: total_items_revenue {
    type: sum
    sql: ${items_revenue} ;;
    value_format_name: usd
  }
}

A correlated subquery over an UNNEST of the same row is evaluated locally by BigQuery — it does not shuffle and it does not multiply rows. This is usually the cheapest option by a wide margin, and it removes a whole class of fanout bugs. Reach for it first and only build option B when someone genuinely needs to filter or group by the array's contents.

The key-value array trap (GA4, event params)

Event tables often store attributes as ARRAY<STRUCT<key STRING, value STRUCT<string_value STRING, int_value INT64>>>. Do not expose that as a generic key/value explore — every metric becomes a filter puzzle and every query unnests the whole array. Pivot the parameters you actually use into named dimensions:

dimension: page_location {
  type: string
  sql: (SELECT p.value.string_value FROM UNNEST(${TABLE}.event_params) AS p WHERE p.key = 'page_location') ;;
}

dimension: engagement_time_msec {
  type: number
  sql: (SELECT p.value.int_value FROM UNNEST(${TABLE}.event_params) AS p WHERE p.key = 'engagement_time_msec') ;;
}

One scalar subquery per field, each one cheap, each one a first-class, documented, filterable dimension. If the list of parameters grows past a dozen, promote the flattening into a scheduled table or a dbt model instead of repeating it in LookML.

3. BigQuery's native JSON type

Columns declared as JSON (not STRING holding JSON) are parsed at write time, so field access is much cheaper than JSON_EXTRACT over text. The two functions to know:

JSON_VALUE(col, '$.path') returns a SQL STRING — use it for dimensions.
JSON_QUERY(col, '$.path') returns JSON — use it when you need a sub-object or an array to unnest.

view: webhook_events {
  sql_table_name: raw.webhook_events ;;

  dimension: event_id {
    primary_key: yes
    type: string
    sql: ${TABLE}.event_id ;;
  }

  dimension: payload_status {
    type: string
    sql: JSON_VALUE(${TABLE}.payload, '$.status') ;;
  }

  dimension: payload_amount {
    type: number
    sql: SAFE_CAST(JSON_VALUE(${TABLE}.payload, '$.amount') AS NUMERIC) ;;
    value_format_name: usd
  }

  dimension_group: payload_occurred {
    type: time
    timeframes: [raw, time, date, week, month, quarter, year]
    sql: SAFE_CAST(JSON_VALUE(${TABLE}.payload, '$.occurred_at') AS TIMESTAMP) ;;
  }

  measure: total_amount {
    type: sum
    sql: ${payload_amount} ;;
    value_format_name: usd
  }
}

Rules that save you incidents:

Always SAFE_CAST. One malformed payload with "amount": "n/a" will otherwise fail every dashboard that touches the field. SAFE_CAST returns NULL instead of erroring.
Cast once, in a dimension; reuse with ${}. Never repeat the JSON_VALUE expression in each measure.
Never build a measure directly on a JSON expression with hand-written SQL aggregation. type: number with SUM(JSON_VALUE(...)) opts out of symmetric aggregates and will be wrong the moment the view is joined on the one side of a one_to_many.
Unnesting a JSON array needs JSON_QUERY plus JSON_QUERY_ARRAY: UNNEST(JSON_QUERY_ARRAY(payload, '$.line_items')). That reintroduces fanout — go back to the options above and decide where it happens.

Schema drift is a modeling problem, not a SQL problem

JSON payloads change without warning. Two defences worth building in on day one:

dimension: payload_keys {
  hidden: yes
  type: string
  sql: ARRAY_TO_STRING(ARRAY(SELECT k FROM UNNEST(JSON_KEYS(${TABLE}.payload, 1)) AS k), ',') ;;
}

Pair that with a data test that fails loudly when a field you depend on stops arriving:

test: payload_status_is_present {
  explore_source: webhook_events {
    column: null_status_count { field: webhook_events.null_status_count }
    filters: [webhook_events.payload_occurred_date: "7 days"]
  }
  assert: status_never_null {
    expression: ${webhook_events.null_status_count} = 0 ;;
  }
}

Run it in CI alongside your other LookML data tests and an upstream schema change becomes a failed pull request instead of a blank dashboard tile.

Cost control: the part nobody budgets for

Nested modeling is where BigQuery bills creep up quietly. Four levers, in the order we apply them on client audits:

Partition and cluster the base table, and make sure your filters survive the unnest. A WHERE on a partition column applied after a cross join with UNNEST in a derived table can stop pruning. Filter inside the derived table's SQL as well as in the Explore, or use sql_always_where on the explore.
Materialise the flatten. A datagroup_trigger-backed PDT that unnests once per day beats hundreds of ad-hoc unnests per dashboard load. See our PDT guide for persistence strategy.
Prefer scalar subqueries over cross joins where you only need aggregates from the array (option C above). No shuffle, no row multiplication.
Watch it in System Activity. Sort explores by average runtime and query count and check whether your nested views are the top line. Monitoring Looker with System Activity walks through the queries.

Make it legible to humans and agents

Nested models are the hardest part of a LookML project for a newcomer — and for a natural-language interface — to interpret, because the field names no longer map one-to-one onto anything a user has seen. Spend the extra ten minutes:

view: order_items {
  label: "Order Items (one row per item)"

  dimension: sku {
    label: "Item SKU"
    description: "SKU of a single line item. One row per item; order-level measures live on the Orders view."
  }
}

Grain stated in the view label, grain restated in the field description, order-level measures hidden or clearly owned elsewhere. That is the same discipline described in Making Your LookML Model AI-Ready, and it matters twice as much here: Conversational Analytics and MCP-connected agents will otherwise happily sum an order total across an item-grain view and report the result with total confidence.

Checklist

Before you ship a view over nested or JSON data:

Column classified as STRUCT, ARRAY, or JSON — and the strategy matches.
Every view has a verified primary_key, including synthetic keys on unnested arrays.
Every join declares an explicit relationship.
No measure contains a raw aggregate wrapped around a JSON_VALUE or UNNEST expression.
All JSON casts use SAFE_CAST.
Flattening is materialised with a datagroup if it is queried often.
Partition filters still prune after the unnest.
Grain is stated in the view label and in field descriptions.
A data test asserts key uniqueness and the presence of critical JSON fields.

Semi-structured data is not an edge case any more; for most Looker estates it is now the majority of the incoming volume. Model it deliberately once and it behaves like any other part of your semantic layer. Model it ad hoc and you get a project full of duplicated unnests, inflated totals, and a BigQuery bill nobody can explain.

If your Looker project is wrestling with event tables, GA4 exports, or JSON payloads — or you want an experienced pair of hands on the flattening layer before it calcifies — get in touch. Vistelio's senior Looker and LookML consultants do this work every week.

Modeling Nested and JSON Data in LookML: ARRAY, STRUCT, and BigQuery's JSON Type