How can I use the "ARRAY_AGG" function twice in sequence in BigQuery? One inside the other?

Question

I'm trying to make a query in BigQuery with the nested ARRAY_AGG function (one inside the other), as follows:

ARRAY_AGG(
  STRUCT(
      .
      .
      .
      .
      .
      ARRAY_AGG(
        STRUCT(
          .
        )
      ) as xxx
    )  
  ) as xxx

However, I am getting the following error:

Aggregations of aggregations are not allowed at [44:3]

How can I rewrite this BigQuery query to return an aggregation of one category with the other in the BigQuery output JSON?

I tried to guide myself by the following Medium article:
https://towardsdatascience.com/bigquery-creating-nested-data-with-sql-727b761f1755

But I am unexpectedly getting some different types of errors when trying to return the aggregation in other ways.

Answer 1

For creating an array in array, the best way is to use a Sub-SELECT.

First we unnest the table tbl and then we build this table again.

Please query each CTE (tbl and tbl2) first, this makes it more clear.

with tbl as (Select 1 as x,[struct(2 as y, [123,456,789] as z), struct(3,[301,302])] as lst),
tbl2 as (Select x, A.y, z from tbl, unnest(lst) as A, unnest(A.z) as z)

#select * from tbl2 # run this query first
# Then this:
Select x, array_Agg(struct(y,Z))
from
(
  select x,y,array_agg(z) as Z
  from tbl2
  group by 1,2
)
group by 1