title: Error: Scalar subquery produced more than one element
body: <p>We just started migrating our queries from Legacy to Standard SQL so we are learning on how to process nested data and arrays now. </p>
  
  <p>Basically what we want to do is to retrieve from <code>ga_sessions</code> table the following data:</p>
  
  <pre><code>visitor id, session id, array of skus
  visitor 1, session 1, [sku_0, sku_1, (...), sku_n]
  visitor 1, session 2, [skus]
  </code></pre>
  
  <p>To do so we ran this simple query:</p>
  
  <pre><code>  WITH
    customers_data AS(
    SELECT
      fullvisitorid fv,
      visitid v,
      ARRAY_AGG((
        SELECT
          prods.productsku
        FROM
          UNNEST(hits.product) prods)) sku
    FROM
      `dataset_id.ga_sessions_*`,
      UNNEST(hits) hits
    WHERE
      1 = 1
      AND _table_suffix BETWEEN FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY))
      AND FORMAT_DATE("%Y%m%d", DATE_SUB(CURRENT_DATE(), INTERVAL 0 DAY))
      --and (select count(productsku) from unnest(hits.product) where productsku is not null) = 1
    GROUP BY
      fv,
      v
    LIMIT
      100 )
  SELECT
    *
  FROM
    customers_data
  </code></pre>
  
  <p>But we get this error:</p>
  
  <pre><code>Error: Scalar subquery produced more than one element
  </code></pre>
  
  <p>The data that comes from the <code>hits</code> field looks something like this:</p>
  
  <p><a href="https://i.stack.imgur.com/AO6Bu.png" rel="noreferrer"><img src="https://i.stack.imgur.com/AO6Bu.png" alt="enter image description here"></a></p>
  
  <p>So when we addded back the <code>where</code> clause:</p>
  
  <pre><code>and (select count(productsku) from unnest(hits.product) where productsku is not null) = 1
  </code></pre>
  
  <p>It does not give an error but the results have duplicated skus and we also lost the skus inside the bigger arrays.</p>
  
  <p>Is there some mistake in our query preventing the arrays of being unnested?</p>