google-bigquerynested-tablearray-agg

Is it possible to use star expression inside array_agg(struct expressions in BigQuery?

I am joining several tables into one nested, and I was wondering if I could use a star expression, for example,

SELECT t1.*, ARRAY_AGG(STRUCT(t2.*)) FROM t1 JOIN t2 on t1.col1=t2.col1 GROUP BY t1.*

I get an error, but I was wondering, maybe I could use some trick for that?

Solution

  • There are two problems with your example:

    1. ARRAY_AGG is an an aggregate function, and as such requires a GROUP BY clause.
    2. The interior of your ARRAY_AGG needs a SELECT - i.e., ARRAY_AGG((SELECT AS STRUCT t2.*)). The extra parentheses are needed to treat the inside query as a scalar subquery expression.

    If you fix those it should be able to run, although I would also suggest aliasing the ARRAY_AGG field to make it more readable.