title: BigQuery Nested Table if Key is null remove from nested table
body: <p>Easy question (hope so)</p>
  
  <p>I have a nested table, which looks like this:</p>
  
  <p><a href="https://i.stack.imgur.com/g5XAZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/g5XAZ.png" alt="enter image description here"></a></p>
  
  <p>AND WHEN DWHKey is null product array should look like this for this row (empty instead of null):</p>
  
  <pre><code>"product": []
  </code></pre>
  
  <p>Do not Work ((</p>
  
  <pre><code>ARRAY_AGG(pr IGNORE NULLS) as product
  </code></pre>
  
  <p>THIS JOIN WHAT I'M DOING:</p>
  
  <pre><code>SELECT 
                          x.SourceSystemCode
                          ...
                          ,product
                          ,payment
                      FROM (
                          SELECT 
                              he.SourceSystemCode
                              ...
                              ,ARRAY_AGG(pr IGNORE NULLS) as product
                      FROM header_table as he
                      LEFT JOIN product_table AS pr ON pr.DWHKey = he.DWHKey
                      GROUP BY he.SourceSystemCode..
                      ) x
                      JOIN (
                          SELECT 
                              he.SourceSystemCode
                              ...
                              ,ARRAY_AGG(pay IGNORE NULLS) AS payment  
                          FROM header_table as he
                          LEFT JOIN payment_table AS pay ON pay.DWHKey = he.DWHKey
                          GROUP BY he.SourceSystemCode...
                      ) y
                      ON x.DWHKey = y.DWHKey
  </code></pre>