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

Question

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?

Why do you want to use legacy SQL for your query? The only similar function is NEST but you can't apply it to multiple columns at once, only individually. — Elliott Brossard, May 22 '17 at 13:04
Due to performance issues - the legacy is faster. I've noticed this issue with NEST, but is there a STRUCT equivalent? — David Rabinowitz, May 22 '17 at 13:54
Can you please [file a bug](https://issuetracker.google.com/issues/new?component=187149&template=0)? If you find that a legacy SQL query is faster than its standard SQL equivalent, it indicates a bug. — Elliott Brossard, May 22 '17 at 16:27

score 3 · Accepted Answer · answered May 22 '17 at 13:55

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.

score 1 · Answer 2 · answered May 22 '17 at 18:59

Meantime, if you still need to have it in BigQuery Legacy SQL - see simple example below.

BigQuery Standard SQL version

#standardSQL
WITH t AS (
  SELECT 1 AS a, 11 AS x, 12 AS y, 13 AS z UNION ALL
  SELECT 2 AS a, 21 AS x, 22 AS y, 23 AS z UNION ALL
  SELECT 3 AS a, 31 AS x, 32 AS y, 33 AS z
)
SELECT 
  a, ARRAY_AGG(STRUCT(x, y, z)) AS aa 
FROM t
GROUP BY a

BigQuery Legacy SQL version (make sure you set destination table and set flatten result off - otherwise UI will flatten output)

#legacySQL
SELECT a, aa.*
FROM JS( 
  ( // input table 
  SELECT 
    a, GROUP_CONCAT(CONCAT(STRING(x), ';', STRING(y), ';', STRING(z))) AS aa 
  FROM 
  (SELECT 1 AS a, 11 AS x, 12 AS y, 13 AS z),
  (SELECT 2 AS a, 21 AS x, 22 AS y, 23 AS z),
  (SELECT 3 AS a, 31 AS x, 32 AS y, 33 AS z)
  GROUP BY a
  ), 
  a, aa, // input columns 
  "[ // output schema 
  {name: 'a', type:'integer'},
  {name: 'aa', type:'record', mode:'repeated', 
  fields: [
    {name: 'x', type: 'integer'},
    {name: 'y', type: 'integer'},
    {name: 'z', type: 'integer'}
    ]}
   ]", 
  "function(row, emit) { // function 
    var aa = []; 
    aa1 = row.aa.split(',');
    for (var i = 0; i < aa1.length; i++) { 
      aa2 = aa1[i].split(';');
      aa.push({x:parseInt(aa2[0]), y:parseInt(aa2[1]), z:parseInt(aa2[2])}); 
    }; 
    emit({
      a: row.a, 
      aa: aa
      }); 
  }"
)

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

2 Answers2