Array Operations Basics

Explore array operations in BigQuery and understand what arrays are, why they are used, and how to work with them effectively. Arrays are especially common in nested and repeated fields, such as those found in GA4 datasets, and mastering array functions is essential for accurate analysis.

Core Array Functions

BigQuery provides several functions for working with arrays:

• ARRAY_LENGTH(array) returns the number of elements in an array. This is useful for understanding the size of repeated fields, such as the number of prices or items associated with a record;
• UNNEST(array) converts array elements into individual rows. This step is critical when working with nested data, as it enables standard SQL operations like filtering, counting, and aggregation. Without flattening, array data remains a single complex field and cannot be analyzed properly;
• ARRAY_AGG(expression) collects multiple values into a single array. It is commonly used after flattening and processing data to regroup values into a compact structure for storage or final output.

Why Flattening Matters

Nested data, such as event parameters or product details, is often stored in arrays. To perform meaningful analysis, arrays must be flattened using UNNEST. Without this step, operations like COUNT() or type conversions cannot be applied correctly because the data is treated as a single entity rather than individual elements.

Arrays in Practice

Common patterns when working with arrays include:

• Measuring array size using ARRAY_LENGTH(product_prices);
• Flattening nested values with UNNEST(product_prices) to analyze each element separately;
• Re-aggregating processed values using ARRAY_AGG to rebuild structured results.

Working with arrays is a routine task in BigQuery when dealing with nested or repeated data. Understanding how to flatten arrays and apply array functions enables efficient, scalable, and accurate data analysis.