title: BigQuery join a nested table onto another table
body: <p>I am trying to join a table of some project data (table1) with a nested array of project id's <strong>onto</strong> another table with project data (table2) (<strong>order is important here</strong>)</p>
  <pre><code>table1
  proj_date     num_proj_per_day     proj_size     proj_id(nested)
  1/1/2020      4                    150           a123
                                                   b456
                                                   c789
  table2
  proj_id(not nested)    proj_loc       lots_of_other_proj_fields....
  a123                   Los Angeles
  b456                   New York
  c798                   Los Angeles
  d012                   Denver
  ....                   ....
  </code></pre>
  <p>desired outcome</p>
  <pre><code>proj_date     num_proj_per_day     proj_size     proj_id(unnested)   pro_loc    
  1/1/2020      4                    150           a123                Los Angeles
  1/1/2020      4                    150           b456                New York
  1/1/2020      4                    150           c789                Los Angeles
  </code></pre>
  <p>I have been able to achieve this outcome if I write the sql code with table1 as the from and then cross join unnest(proj_id) and then left join table2. <strong>The problem is i need to have table2 in the from statement then join table1 on the unnested(proj_id)</strong>. Order unfortuantely matters because I have to merge this new dataset(table1) into existing dataset/framework(table2) within Looker</p>
  <p>Example of what works to get the correct outcome but does not work for my application</p>
  <pre><code>SELECT 
   table1.*,
   table2.proj_loc
  FROM table1
  CROSS JOIN UNNEST(table1.proj_id) as unnested
  LEFT JOIN table2
   ON table2.proj_id = unnested.proj_id
  </code></pre>
  <p>I am looking for something like below but you can not put the unnest into the ON clause - bigquery pops error &quot;Unexpected keyword UNNEST&quot;</p>
  <pre><code>SELECT 
   table1.*,
   table2.proj_loc
  FROM table2
  LEFT JOIN table1
   ON UNNEST(table1.proj_id)=table2.proj_id
  </code></pre>
  <p>Thank you in advance and let me know if you need anymore clarifying information</p>