Calculate aggregates based on fields in BigQuery's repeated records

Question

{
"outer_1": "1",
"outer_2": 2,
"inner": [
  {
    "inner_1": 0,
    "inner_2": null,
    },
  {
    "inner_1": 3,
    "inner_2": true,
  },
 ]
}

Above you can see roughly what my data looks like. In terms of BigQuery, inner is a repeated record. I would like to calculate aggregates (like the sum of non-null inner_1 fields) for each row.

One idea is to use unnest(inner), calculate aggregates and then group by all other columns. This doesn't seem, however, to be the optimal approach. I already have about a million rows. Unnesting would create tremendously many more rows and doing aggregation to group everything right away doesn't sound right.

I bet there might be a more efficient way to iterate over nested records and calculate aggregates for them.

In the end, I would like to have something like:

    {
"outer_1": "1",
"outer_2": 2,
"inner_1_sum: 3, 
"inner_2_non_null_count": 1
}

user_5221944 · Answer

Consider below approach

select * except(inner_col),
  ( select as struct 
      sum(inner_1) as inner_1_sum,
      countif(not inner_2 is null) as inner_2_non_null_count
    from unnest(inner_col)
  ).*
from `project.dataset.table`

If applied to sample data in your question - output is

Calculate aggregates based on fields in BigQuery's repeated records

1 Answers