Unnest (Array)

To separate an array into its individual components, we can make use of the UNNEST function in BigQuery. The syntax below outlines how to use this function:

SELECT 
  column(s),
  new_column_name
FROM 
  table_name,
  UNNEST(array_column_name) AS new_column_name

OR

SELECT 
  column(s),
  new_column_name
FROM 
  table_name,
  UNNEST(hits) AS hits


The syntax has two significant components, where the UNNEST function is utilized in the FROM clause and the new column is included in the SELECT clause.

In this sample table, we will perform an UNNEST on the column samples_array, and we will unnest it into a column named samples_individual.

So the UNNEST(samples_array) will be in the FROM clause. After that, we can now add the samples_column in the SELECT clause.

We can improve our previous query and use UNNEST to convert the arrays into individual rows.

SELECT
  category,
  samples_individual
FROM
  colors,
  UNNEST(samples_array) AS samples_individual
;

Functions such as sorting and joining on the new column is now possible after using the UNNEST.

However, you will notice in the results that the empty array was not included (The Void). This is the default behavior of UNNEST, which is to remove rows with empty arrays.

Not to worry! We can adjust our query to still include these rows in the following section.