Datawise
BigQuery Arrays & Structs

Pay attention to cardinality & grain when UNNESTING in BigQuery!

Constantin LunguUpdated 1 min read

Photo by Joshua Tsu on Unsplash

Whenever you're UNNESing an ARRAY, you're getting a Cartesian product between the row and the array contents. If you were to unnest another array, you'll get another Cartesian product between the output of the previous unnest and the elements in the current array.

Let's look at an example. A student has their grades stored in an ARRAY as well as their food allergies in another ARRAY.

If we are to UNNEST both array we'll end having count_of_grades x count_of_allergies rows for each student, 4x3 in this case.

Why this happens? Well the allergies and grades have no relationship between each other, they just refer to the same student row.

Take this into account when you're working with nested data.

SELECT
  'Joe Doe' AS name,
  [90, 58, 50, 100] AS grades,
  ['gluten','milk', 'eggs'] AS allergies

BigQuery results: one row for Joe Doe with the grades array 90, 58, 50, 100 and the allergies array gluten, milk, eggs.

SELECT
  name,
  grade,
  food_allergy
FROM input_data

LEFT JOIN UNNEST(grades) AS grade
LEFT JOIN UNNEST(allergies) AS food_allergy

BigQuery results: 12 rows for Joe Doe, pairing each grade (90, 58, 50, 100) with each food_allergy (gluten, milk, eggs).

Enjoyed this? Here are some related articles you might find useful: