title: What are the semantics of SQL 'FROM UNNEST'?
body: <p>When reading an expression in a formal language, I'm used to reading from the inside out, i.e. understanding subexpressions and building up to the whole. In <a href="https://stackoverflow.com/a/51563922/1908650">this SQL snippet</a>:</p>
  
  <pre><code>SELECT 
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'TITLE') AS level_id,
    (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'url') AS url
  FROM `events_20180725`
  WHERE event_name = 'SCI_ERROR'
  </code></pre>
  
  <p>One subexpression is </p>
  
  <pre><code>SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'TITLE'
  </code></pre>
  
  <p>That's not a normal subquery: if I were to try to run it on its own, I would get an error because <code>event_params</code> isn't an array. So it seems that </p>
  
  <ul>
  <li><code>UNNEST</code> can be used with something other than arrays.</li>
  <li>There is some kind of binding of the table <code>events_20180725</code> used in the outer <code>FROM</code> which makes it accessible to the <code>UNNEST</code> inside the subquery.</li>
  </ul>
  
  <p><a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays" rel="nofollow noreferrer">https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays</a> contains some examples (under 'Querying Nested Arrays'), but doesn't actually explain the semantics. What's going on here?</p>