Search code examples
google-bigquerylegacy-sql

ARRAY_AGG(STRUCT(x,y,z)) equivalent in Bigquery legacy SQL

I have a standard SQL query of the following structure

SELECT a, ARRAY_AGG(STRUCT(x,y,z))
FROM t
GROUP BY a

How can write the same query in legacy SQL?

Solution

  • It is not possible to NEST non-leaf fields using Legacy SQL. The only workaround would be to pack x,y,z into a string (for example constructing JSON), then use NEST on it, and whenever individual fields are needed, use some string parsing function or Javascript UDF. Needless to say, it is much simpler with Standard SQL.