--- title: BigQuery Tip: The UNNEST Function | daily.dev description: Firebase Analytics exports data into BigQuery using nested arrays and structs rather than flat tables, bundling user properties and event parameters directly... canonical: https://daily.dev/posts/bigquery-tip-the-unnest-function-tluxfsxdu twitter:card: summary_large_image twitter:site: @dailydotdev og:type: website og:site_name: daily.dev og:title: BigQuery Tip: The UNNEST Function | daily.dev og:description: Firebase Analytics exports data into BigQuery using nested arrays and structs rather than flat tables, bundling user properties and event parameters directly... og:url: https://daily.dev/posts/bigquery-tip-the-unnest-function-tluxfsxdu og:image: https://api.daily.dev/og/posts/TlUxfSxDu.png og:image:alt: BigQuery Tip: The UNNEST Function og:image:width: 1200 og:image:height: 630 og:locale: en --- > ## Documentation Index > Fetch the complete documentation index at: https://daily.dev/llms.txt > Use this file to discover all available pages before exploring further. # BigQuery Tip: The UNNEST Function **[Firebase](https://daily.dev/sources/firebase-blog)** · 8 min read · 0 upvotes · 0 comments ## Summary Firebase Analytics exports data into BigQuery using nested arrays and structs rather than flat tables, bundling user properties and event parameters directly into each row to avoid JOINs. This tutorial explains how to use BigQuery's standard SQL UNNEST function to flatten these arrays into queryable rows, walking through examples from a simple array of primes to querying Firebase Analytics events and user properties by specific criteria, including combining multiple UNNEST calls in a single query. ## Full article daily.dev links to this article rather than hosting it. Read it at the original source: ## Questions this post answers ### How do I query nested event parameters in Firebase Analytics data exported to BigQuery? Use the UNNEST function to flatten the nested arrays of structs into individual rows. For example, UNNEST(event_dim) as event breaks out each event, and a further UNNEST(event.params) as event_param flattens the event parameters, letting you filter with WHERE clauses like event.name = "round_completed" AND event_param.key = "score". _Developers wiring up analytics pipelines can find more BigQuery query patterns like this on daily.dev._ ### Why do I get a 'Cannot access field name on a value with type ARRAY' error in BigQuery standard SQL? This error occurs when trying to access a field directly on an array of structs instead of first flattening it. BigQuery cannot compare a string to a struct element buried inside an array without unnesting it first; the fix is to use CROSS JOIN UNNEST(field) as alias, or the shorthand comma syntax, before referencing the nested field in a WHERE clause. _Debugging nested query errors gets faster when you can search real examples on daily.dev._ ### How does BigQuery structure Firebase Analytics export data with arrays and structs? Each row in the Firebase Analytics BigQuery export bundles multiple events together, with event parameters and user properties stored as arrays of structs rather than separate joinable tables. A single row can contain 2 or 3 events, each with its own array of parameters, plus an array of user properties, eliminating the need for JOINs across tables. _Understanding this schema helps when daily.dev readers debug their own analytics pipelines._ ## Similar posts on daily.dev - [First-party analytics with SQLite](https://daily.dev/posts/first-party-analytics-with-sqlite-p5rk4jwpz) · Flavio Copes · 1 upvotes · 0 comments - [New Firestore query engine enables pipelines](https://daily.dev/posts/new-firestore-query-engine-enables-pipelines-qveysfcui) · Google Cloud · 0 upvotes · 0 comments - [From Raw Tables to Conversational Analytics in One Afternoon](https://daily.dev/posts/from-raw-tables-to-conversational-analytics-in-one-afternoon-llmaos62g) · dltHub · 0 upvotes · 0 comments - [Google BigQuery gets managed AI functions to simplify unstructured data analysis](https://daily.dev/posts/google-bigquery-gets-managed-ai-functions-to-simplify-unstructured-data-analysis-jhmzgvsac) · InfoWorld · 0 upvotes · 0 comments --- Tags: [#sql](https://daily.dev/tags/sql), [#firebase](https://daily.dev/tags/firebase), [#google-bigquery](https://daily.dev/tags/google-bigquery) [View this post on daily.dev](https://daily.dev/posts/bigquery-tip-the-unnest-function-tluxfsxdu) ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://daily.dev/#organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180},"sameAs":["https://twitter.com/dailydotdev","https://github.com/dailydotdev","https://www.linkedin.com/company/daily-dev-ltd"]},{"@type":"WebSite","@id":"https://daily.dev/#website","url":"https://daily.dev","name":"daily.dev","publisher":{"@id":"https://daily.dev/#organization"},"potentialAction":{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https://daily.dev/search?q={search_term_string}"},"query-input":"required name=search_term_string"}}]} {"@context":"https://schema.org","@type":"TechArticle","headline":"BigQuery Tip: The UNNEST Function","url":"https://daily.dev/posts/bigquery-tip-the-unnest-function-tluxfsxdu","mainEntityOfPage":{"@type":"WebPage","@id":"https://daily.dev/posts/bigquery-tip-the-unnest-function-tluxfsxdu"},"datePublished":"2026-08-23T12:21:23.134Z","dateModified":"2026-08-23T12:36:28.882Z","description":"Firebase Analytics exports data into BigQuery using nested arrays and structs rather than flat tables, bundling user properties and event parameters directly...","image":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/7ed721de96163685d26ab7e314be3ebc?_a=AQAEuop","thumbnailUrl":"https://media.daily.dev/image/upload/f_auto,q_auto/v1/posts/7ed721de96163685d26ab7e314be3ebc?_a=AQAEuop","isAccessibleForFree":true,"articleSection":"Firebase","inLanguage":"en","publisher":{"@type":"Organization","name":"daily.dev","url":"https://daily.dev","logo":{"@type":"ImageObject","url":"https://daily.dev/apple-touch-icon.png","width":180,"height":180}},"author":{"@type":"Organization","name":"Firebase","logo":"https://media.daily.dev/image/upload/s--i395oqZH--/f_auto,q_auto/v1787487448/logos/firebase-blog?_a=BAMAMicg0","url":"https://daily.dev/sources/firebase-blog"},"commentCount":0,"discussionUrl":"https://daily.dev/posts/bigquery-tip-the-unnest-function-tluxfsxdu","interactionStatistic":[{"@type":"InteractionCounter","interactionType":{"@type":"LikeAction"},"userInteractionCount":0},{"@type":"InteractionCounter","interactionType":{"@type":"CommentAction"},"userInteractionCount":0}],"keywords":"sql,firebase,google-bigquery","timeRequired":"PT8M"} {"@context":"https://schema.org","@type":"BreadcrumbList","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https://daily.dev"},{"@type":"ListItem","position":2,"name":"Firebase","item":"https://daily.dev/sources/firebase-blog"},{"@type":"ListItem","position":3,"name":"BigQuery Tip: The UNNEST Function"}]} {"@context":"https://schema.org","@type":"FAQPage","@id":"https://daily.dev/posts/bigquery-tip-the-unnest-function-tluxfsxdu#faq","mainEntity":[{"@type":"Question","name":"How do I query nested event parameters in Firebase Analytics data exported to BigQuery?","acceptedAnswer":{"@type":"Answer","text":"Use the UNNEST function to flatten the nested arrays of structs into individual rows. For example, UNNEST(event_dim) as event breaks out each event, and a further UNNEST(event.params) as event_param flattens the event parameters, letting you filter with WHERE clauses like event.name = \"round_completed\" AND event_param.key = \"score\". Developers wiring up analytics pipelines can find more BigQuery query patterns like this on daily.dev."}},{"@type":"Question","name":"Why do I get a 'Cannot access field name on a value with type ARRAY' error in BigQuery standard SQL?","acceptedAnswer":{"@type":"Answer","text":"This error occurs when trying to access a field directly on an array of structs instead of first flattening it. BigQuery cannot compare a string to a struct element buried inside an array without unnesting it first; the fix is to use CROSS JOIN UNNEST(field) as alias, or the shorthand comma syntax, before referencing the nested field in a WHERE clause. Debugging nested query errors gets faster when you can search real examples on daily.dev."}},{"@type":"Question","name":"How does BigQuery structure Firebase Analytics export data with arrays and structs?","acceptedAnswer":{"@type":"Answer","text":"Each row in the Firebase Analytics BigQuery export bundles multiple events together, with event parameters and user properties stored as arrays of structs rather than separate joinable tables. A single row can contain 2 or 3 events, each with its own array of parameters, plus an array of user properties, eliminating the need for JOINs across tables. Understanding this schema helps when daily.dev readers debug their own analytics pipelines."}}]} ```