title: How to flatten an array with UNNEST or any other functions?
body: <p>Suppose I get a temporary table with one field is an array, how to turn it to multiple rows ?</p>
  
  <p>With PostgreSQL this can be done with UNNEST <a href="http://sqlfiddle.com/#!15/21673/19" rel="noreferrer">http://sqlfiddle.com/#!15/21673/19</a></p>
  
  <pre><code>WITH x AS (SELECT ARRAY[1,3,2] AS arr)
  
  SELECT UNNEST(arr) FROM x
  </code></pre>
  
  <p>Run the same query in BigQuery says <em>Syntax error: Unexpected
  keyword UNNEST at [3:8]</em></p>
  
  <p>it seems in BigQuery the UNNEST can only be put after FROM clause,</p>
  
  <p>Then I have tried these:</p>
  
  <pre><code>WITH x AS (SELECT ARRAY[1,3,2] AS arr)
  
  SELECT * FROM UNNEST(x)
  </code></pre>
  
  <p>this one says <em>UNNEST cannot be applied on a table: x at [3:22]</em>; or this</p>
  
  <pre><code>WITH x AS (SELECT ARRAY[1,3,2] AS row)
  
  SELECT * FROM UNNEST(x.arr)
  </code></pre>
  
  <p>says <em>UNNEST cannot be applied on a table: x.arr at [3:22]</em></p>
  
  <p>BTW, current temporary table x looks like this:</p>
  
  <pre><code>WITH x AS (SELECT ARRAY[1,2] AS row)
  
  SELECT * FROM x
  EOF
  
  +--------------+
  |     row      |
  +--------------+
  | [u'1', u'2'] |
  +--------------+
  </code></pre>
  
  <p>while I am expecting to turn it into rows of value:</p>
  
  <pre><code>+-----+
  | row |
  +-----+
  |   1 |
  |   2 |
  +-----+
  </code></pre>
  
  <p><a href="https://cloud.google.com/bigquery/sql-reference/arrays" rel="noreferrer">https://cloud.google.com/bigquery/sql-reference/arrays</a></p>