google-bigquerygoogle-cloudsql

Advanced

Ben Gorman

Ben Gorman

Life's a garden. Dig it.

Skip to the problems ->

Arrays

BigQuery allows you to create columns of arrays. For example, if you have a table of vehicles, you might want to include a tags column, so that each vehicle can have zero or more arbitrary tags.

vehicles

See the code to build this table

SELECT "2019 Honda Accord" as vehicle, ["sun roof", "leather seats"] AS tags,
UNION ALL SELECT "2021 Ford Ranger" as vehicle, [] AS tags,
UNION ALL SELECT "2007 Toyota Camry" as vehicle, ["CD player"] AS tags,
UNION ALL SELECT "2010 Nissan Altima" as vehicle, ["moon roof", "bluetooth"] AS tags,
UNION ALL SELECT "2021 Ford F-150" as vehicle, ["crash detection", "foot step", "sirius xm"] AS tags

Arrays can be an efficient way to store and query data, as they reduce the need to join tables. In the vehicles table above, we could fetch vehicles with a moon roof like so

SELECT * FROM vehicles 
WHERE "moon roof" IN UNNEST(tags)

Array Notes

  • When you have a column of arrays, each array must have the same type (e.g. INT, FLOAT, STRING)
  • You can't have an array of arrays