title: Join a table with a repeated field using UNNEST()
body: <p>I am trying to join two tables, one with a repeated field, using the Standard SQL in BigQuery. Using Legacy SQL I came up with this query</p>
  
  <p><strong>Legacy SQL</strong>:</p>
  
  <pre><code>SELECT
    b.*,
    t.field1,
    t.field2
  FROM
    FLATTEN([table1],repeated_field) AS b
  LEFT JOIN
    [table2] AS t
  ON
    b.Row = t.RowLabel
    b.seat = t.SeatLabel
  </code></pre>
  
  <p>The repeated field is the <code>seat</code>. I tried using <code>unnest()</code> and looking at the <a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/migrating-from-legacy-sql" rel="nofollow noreferrer">migration guide</a>, but could not come up with a query myself. Help appreciated thanks. </p>