title: Remove duplicates from an array in BigQuery
body: <p>I work within BigQuery, and one of my tables has an array that has duplication that I want to figure out how to remove. I'm able to identify the problem rows using a CTE, but actually removing them is where I get stuck.</p>
  <p>Let me provide an example to give more context:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Order</th>
  <th>Item.Sku</th>
  <th>Item.Quantity</th>
  <th>Item.Price</th>
  <th>Shipment.Ship_Number</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>123</td>
  <td>ABC</td>
  <td>2</td>
  <td>5.99</td>
  <td>UPS123</td>
  </tr>
  <tr>
  <td></td>
  <td>ABC</td>
  <td>2</td>
  <td>5.99</td>
  <td>UPS234</td>
  </tr>
  <tr>
  <td></td>
  <td>XYZ</td>
  <td>1</td>
  <td>19.99</td>
  <td></td>
  </tr>
  <tr>
  <td>456</td>
  <td>ABC</td>
  <td>2</td>
  <td>5.99</td>
  <td>UPS456</td>
  </tr>
  <tr>
  <td>789</td>
  <td>XYZ</td>
  <td>1</td>
  <td>19.99</td>
  <td>UPS789</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>So looking at this table (for code, going to call it Order_Table), I would want to delete only the second &quot;row&quot; within the Item array for order 123 - since the sku, quantity, and price are all exactly the same. Although Orders 456 and 789 have the same information in the Item array, they are not considered duplicates because they have different order numbers. I also included an additional array, Shipment, because I'm dealing with a table with more than one array, so I want to make sure any solution takes that into account. So after duplicate deletion, I'd like to end up with this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>Order</th>
  <th>Item.Sku</th>
  <th>Item.Quantity</th>
  <th>Item.Price</th>
  <th>Shipment.Ship_Number</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>123</td>
  <td>ABC</td>
  <td>2</td>
  <td>5.99</td>
  <td>UPS123</td>
  </tr>
  <tr>
  <td></td>
  <td>XYZ</td>
  <td>1</td>
  <td>19.99</td>
  <td>UPS234</td>
  </tr>
  <tr>
  <td>456</td>
  <td>ABC</td>
  <td>2</td>
  <td>5.99</td>
  <td>UPS456</td>
  </tr>
  <tr>
  <td>789</td>
  <td>XYZ</td>
  <td>1</td>
  <td>19.99</td>
  <td>UPS789</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Any ideas how to get there? If you have any questions feel free to ask and I'd be happy to provide more context. Thanks!</p>
  <p>EDIT: This is how I identified the problem rows using a CTE:</p>
  <pre><code>select * from
  (select item.*
  ,row_number() over (
    partition by 
      order,
      item.sku,
      item.quantity,
      item.price
    order by item.sku)
      as row_id
      from Order_Table t, t.Item item)
    where row_id &gt; 1
  </code></pre>