Course contentsShow
← Cloud and Infrastructure
Lesson 2666 of 2,98957. Serverless Data ProcessingFree lesson

BigQuery Standard SQL and Functions

Writing queries in BigQuery using Standard SQL, built-in functions, and array/struct operations.

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.