Vaibhav Verma

We’ve started using Google BigQuery extensively at Cruise as a data warehouse. The syntax for querying arrays in BigQuery isn’t obvious and this post will explain how it works.

Consider a table that stores a company and its executives with the following schema:

companies
- name
- founding_year
- executives
    * name
    * title

with some sample data

WITH companies AS ( 
   SELECT 
      "Apple" as name, 1976 as founding_year,
      [
        (SELECT AS STRUCT 'Tim Cook' name, 'CEO' title),
        (SELECT AS STRUCT 'Jony Ive' name, 'Chief Design Officer' title),
        (SELECT AS STRUCT 'Jeff Williams' name, 'COO' title)

      ] as executives UNION ALL SELECT
      
      "Amazon" as name, 1994 as founding_year,
      [
        (SELECT AS STRUCT 'Jeff Bezos' name, 'CEO' title),
        (SELECT AS STRUCT 'Brian T. Olsavsky' name, 'CFO' title)
      ] as executives UNION ALL SELECT
      
      "Twitter" as name, 2006 as founding_year,
      [
        (SELECT AS STRUCT 'Jack Dorsey' name, 'CEO' title),
        (SELECT AS STRUCT 'Ned Segal' name, 'CFO' title)
      ] as executives UNION ALL SELECT
     
      "AirBNB" as name, 2008 as founding_year,
      [
        (SELECT AS STRUCT 'Brian Chesky' name, 'CEO' title),
        (SELECT AS STRUCT 'Joe Gebbia' name, 'CPO' title)
      ] as executives UNION ALL SELECT
      
      "Square" as name, 2009 as founding_year,
      [
        (SELECT AS STRUCT 'Jack Dorsey' name, 'CEO' title)
      ] as executives
      
) SELECT * FROM companies;

We can use familiar SQL queries to query top level fields like name or founding_year

SELECT * FROM companies WHERE founding_year > 2000;

To query nested fields like executives.name or executives.title, we use a combination of CROSS JOIN and UNNEST.

For example, this query returns all companies with Jack Dorsey as an executive:

SELECT * FROM companies CROSS JOIN UNNEST(executives) as executive WHERE executive.name = 'Jack Dorsey';

To understand how this query works, notice that CROSS JOIN and UNNEST return the row with the flat value of each item in the array.

For example, consider this query

SELECT c.*, executive FROM companies c CROSS JOIN UNNEST(c.executives) as executive

This returns a row for each combination of company and executive.

Once, we have this table, it’s easy to see how WHERE clauses work like we expect on the executive.name and executive.title fields.

Given the results above - what would this query return?

SELECT c.name, executive.name as ceo FROM companies c CROSS JOIN UNNEST(c.executives) as executive WHERE executive.title='CEO'

If you guessed right, it returns the name and CEO of each company in our table.

Tip: You can use a comma in place of CROSS JOIN in your queries for brevity.

SELECT c.* FROM companies c CROSS JOIN UNNEST(executives) as executive WHERE executive.name = 'Jack Dorsey';

is equivalent to

SELECT c.* FROM companies c, UNNEST(executives) as executive WHERE executive.name = 'Jack Dorsey';

I hope this helps you explore datasets in BigQuery more easily.

BigQuery has worked well for us as a data warehouse. From engineers to data analysts and managers, I’ve seen users of all technical abilities have use BigQuery at Cruise in their day-to-day work to build a self-driving robotaxi.