Internal error on NEST when not flattening results

Question

I'm trying to GROUP and return a repeated field into a new table

SELECT url, NEST(label) AS labels
FROM [mytable]
GROUP EACH BY url

It works when I've got the "Flatten Results" checkbox checked. When I uncheck that box I get 'Error: An internal error occurred and the request could not be completed.'

ontology-knowledge-graph:job_qD7a2Wrq9uCTqZrMbvwdy3v9Vtg

Answer 1

NEST is unfortunately incompatible with unflattened results, as also mentioned here.

A workaround that might work for you is using SPLIT(GROUP_CONCAT(label)) instead of using NEST. That should work if your label field is of type string. You may need to choose an explicit separator for GROUP_CONCAT if your labels contain commas, but I think this solution should be workable.

Answer 2

Recently found workaround for this issue:

Try

SELECT url, labels 
FROM (
  SELECT url, NEST(label) AS labels
  FROM [mytable]
  GROUP EACH BY url
) as a
CROSS JOIN (SELECT 1) as b  

Note, you have to write result to table with Allow Large Results on and Flatten Results off

Answer 3

From "Query Reference" on NEST() :

BigQuery automatically flattens query results, so if you use the NEST function on the top level query, the results won't contain repeated fields. Use the NEST function when using a subselect that produces intermediate results for immediate use by the same query.

So if you want a non-flattened result here, you'd need to do a select * from your other select I'd think