title: BQ: correlated subqueries that reference other tables are not supported - where ARRAY_AGG is not practical
body: <p>We have leveraged <code>ARRAY</code> and <code>STRUCT</code> a lot in BQ, up to a point where we cannot change the content of arrays due to the error in topic.
  See below a simple example using public data. Assume <code>INNER JOIN</code> will not work because images are missing, on purpose or in error.</p>
  
  <p>Now, I know that normally you can move the <code>left join</code> from the array redefinition to the <code>FROM</code> clause and use <code>ARRAY_AGG</code> but this is not always possible. </p>
  
  <p>In our case, the "other fields" outside of the array-to-update are other arrays or structs - like the <code>github-nested</code> table. </p>
  
  <p>Since you cannot do a <code>SELECT DISTINCT</code> on <code>STRUCT</code> or <code>ARRAY</code> fields, you end up being required to UNNEST everything and re-create the table from scratch with many ARRAY_AGG, lot of resource consumption and risk of OOM. This is impossible for tables with a lot of nested fields.</p>
  
  <pre><code>SELECT
    * EXCEPT(webDetection),
    STRUCT(
      webDetection.partialMatchingImages,
      webDetection.pagesWithMatchingImages,
      webDetection.fullMatchingImages,
      ARRAY(
        SELECT AS STRUCT
          fmi.score,
          fmi.url,
          i.object_id
        FROM
          data.webDetection.fullMatchingImages fmi
        LEFT JOIN
          `bigquery-public-data.the_met.images` i
        ON
          fmi.url = i.original_image_url
        ) AS fullMatchingImages_from_met,
      webDetection.webEntities
    ) AS webDetection
  FROM
    `bigquery-public-data.the_met.vision_api_data` data
  </code></pre>
  
  <p>Any idea how to avoid re-aggregating ?</p>