title: Array concatenation with distinct elements in BigQuery
body: <p>Let's say in each row I have an <code>id</code> and two arrays <code>array_1</code> and <code>array_2</code> that looks like following</p>
  
  <pre><code>SELECT 'a' id, [1,2,3,4,5] array_1, [2,2,2,3,6] array_2 UNION ALL
  SELECT 'b', [2,3,4,5,6], [7,7,8,6,9] UNION ALL
  SELECT 'c', [], [1,4,5]
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/L7reJ.png" width="200" /></p>
  
  <p>I want concatenate these two arrays and only keep the unique elements in the new array. My desired output would look like the following</p>
  
  <pre><code>+----+-----------+-----------+-----------------------------+
  | id |  array_1  |  array_2  | concatenated_array_distinct |
  +----+-----------+-----------+-----------------------------+
  | a  | 1,2,3,4,5 | 2,2,2,3,6 |                 1,2,3,4,5,6 |
  | b  | 2,3,4,5,6 | 7,7,8,6,9 |             2,3,4,5,6,7,8,9 |
  | c  |           |     1,4,5 |                       1,4,5 |
  +----+-----------+-----------+-----------------------------+
  </code></pre>
  
  <p>I was trying to use <code>array_concat</code> function but I could not find a way to keep distinct elements using the <code>array_concat</code> function.</p>
  
  <p>Is there anyway I can get the desired output?</p>