BigQuery Standard SQL and Functions
What You'll Learn
You'll learn how to write expressive queries in BigQuery using Standard SQL syntax, leverage built-in functions, and work with arrays and structs for complex data manipulation.
Core Idea
BigQuery uses Standard SQL (SQL:2011 compliant), which means familiar SQL syntax works out-of-the-box. Beyond basic SELECT statements, BigQuery provides hundreds of built-in functions for transformations, plus powerful support for nested and repeated data through arrays and structs.
Think of BigQuery functions as a Swiss Army knife for data: string manipulation, date arithmetic, statistical calculations, JSON parsing—all optimized for petabyte-scale processing without you managing any infrastructure.
Working with Functions
BigQuery categorizes functions into families:
- String functions:
CONCAT(),LOWER(),REGEXP_EXTRACT() - Date/time functions:
CURRENT_DATE(),DATE_DIFF(),TIMESTAMP_TRUNC() - Aggregate functions:
COUNT(),SUM(),APPROX_QUANTILES() - Math functions:
ROUND(),MOD(),SQRT()
Arrays and Structs
Arrays hold repeated values of the same type. Structs group multiple fields into a record, like a mini-table row.
-- Accessing array elements
SELECT tags[OFFSET(0)] AS first_tag
FROM products;
-- Unnesting arrays (flattening)
SELECT product_id, tag
FROM products, UNNEST(tags) AS tag;
-- Working with structs
SELECT user.name, user.email
FROM events
WHERE user.country = 'US';
This nested data model mirrors how JSON and semi-structured data naturally exist, avoiding expensive joins and preserving relationships within a single table.
Key Takeaway: BigQuery Standard SQL combines familiar SQL syntax with powerful built-in functions and native support for arrays/structs, enabling you to query complex, nested data efficiently at massive scale without managing servers.