BigQuery: NEST alternative in Standard SQL

Question

What is the alternative of NEST function in Standard SQL. I have tried ARRAY_AGG as mentioned here but ARRAY_AGG is returning one row whereas NEST is returning flattened array.

Legacy SQL: NEST

Standard SQL: ARRAY_AGG

Answer 1

Below is for BigQuery:

ARRAY_AGG() in Standard SQL is in fact equivalent of NEST() in Legacy SQL
They both returns array of elements in one row

The difference is in how UI shows result repeated fields - for Legacy SQL UI flattens result and that is why you see it as a "flattened array"

You can test it by running below

#legacySQL  
SELECT COUNT(1) rows FROM (
  SELECT NEST(x) y
  FROM (SELECT 1 AS x),
    (SELECT 2 AS x),
    (SELECT 3 AS x),
    (SELECT 4 AS x),
    (SELECT 5 AS x)
)

with result

Row rows     
1   1    

Have in mind - such flattening in UI happens only for most outer select statement and all inner selects treat NEST as ARRAYS / repeated fields

As of saving result into table - if you want to preserve output of below as repeated field

#legacySQL  
SELECT NEST(x) y
FROM (SELECT 1 AS x),
  (SELECT 2 AS x),
  (SELECT 3 AS x),
  (SELECT 4 AS x),
  (SELECT 5 AS x)

you need to make sure you have disabled Flatten Results for which you also need Allow Large Results