BigQuery NEST() returns 'Error: An internal error occurred' [duplicate]

Question

I'm trying to nest a field in the BigQuery UI (not the API) and continually get hit with an error when trying to output to a table without flattening:

Error: An internal error occurred and the request could not be completed.

I'm using the NEST() function and I've tried this on the public Shakespeare dataset and continue to get the same error.

SELECT corpus, NEST(word) FROM [publicdata:samples.shakespeare] GROUP BY 1

My Job ID is: realself-main:bquijob_1bfb8310_153583ecbc2

Answer 1

There were tons of question on SO related to how to generate repeated fileds/records in BigQuery And, there were many different answers - ranging

from: NEST is not compatible with unflatten result - as in
Internal error on NEST when not flattening results

to: some solutions to address this issue using JS UDF as in
Nest multiple repeated fields in BigQuery ;
Create a table with Record type column ;
create a table with a column type RECORD

there are more - you can search

But surprisingly enough - recently, I found how to make NEST() work almost as it supposed to work!

Try below to see the trick

SELECT corpus, words 
FROM (
  SELECT corpus, NEST(word) AS words
  FROM [publicdata:samples.shakespeare] 
  GROUP BY 1
) 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