Can a field have mode NULLABLE and REPEATED in BigQuery?

google-bigquery

Solution

To represent an array with NULLABLE elements, you can use a structure to wrap them. For example, you could have a column such as:

nullable_arr ARRAY<STRUCT<value INT64>>

Taking things a step further, you can represent a possibly-null array with possibly-null elements using another level of indirection:

nullable_arr STRUCT<value ARRAY<STRUCT<value INT64>>>

The downside, of course, is that it takes more syntax to query. If you wanted to get the sum of the elements in the array defined with the latter type, you would have to do something like this:

SELECT (SELECT SUM(elem.value) FROM UNNEST(nullable_arr.value) AS elem) AS array_sum
FROM MyTable;

For the sake of comparison, taking the sum of a column named `arr` defined as an `ARRAY<INT64>` can be expressed as:

SELECT (SELECT SUM(elem) FROM UNNEST(arr) AS elem) AS array_sum
FROM MyTable;

Problem

Original source