title: How to delete records in BigQuery based on values in an array?
body: <p>In Google BigQuery, I would like to delete a subset of records, based on the value of a specific column. It's a query that I need to run repeatedly and that I would like to run automatically.</p>
  <p>The problem is that this specific column is of the form <em>STRUCT&lt;column_1 ARRAY (STRING), column_2 ARRAY (STRING), ... &gt;</em>, and I don't know how to use such a column in the where-clause when using the delete-command.</p>
  <p>Here is basically what I am trying to do (this code does not work):</p>
  <pre><code>DELETE
  FROM dataset.table t
  LEFT JOIN UNNEST(t.category.column_1) AS type
  WHERE t.partition_date = '2020-07-22'
    AND type = 'some_value'
  </code></pre>
  <p>The error that I'm getting is: <em>Syntax error: Expected end of input but got keyword LEFT at [3:1]</em></p>
  <p>If I replace the DELETE with SELECT *, it does work:</p>
  <pre><code>SELECT *
  FROM dataset.table t
  LEFT JOIN UNNEST(t.category.column_1) AS type
  WHERE t.partition_date = '2020-07-22'
    AND type = 'some_value'
  </code></pre>
  <p>Does somebody know how to use such a column to delete a subset of records?</p>
  <p><strong>EDIT:</strong></p>
  <p>Here is some code to create a reproducible example with some silly data (fill in your own dataset and table name in all queries):</p>
  <p>Suppose you want to delete all rows where category.type contains the value 'food'.</p>
  <p>1 - create a table:</p>
  <pre><code>CREATE TABLE &lt;DATASET&gt;.&lt;TABLE_NAME&gt;
  (
    article STRING,
    category STRUCT&lt;
      color STRING,
      type ARRAY&lt;STRING&gt;
    &gt;
  );
  </code></pre>
  <p>2 - Insert data into the new table:</p>
  <pre><code>INSERT  &lt;DATASET&gt;.&lt;TABLE_NAME&gt;
  SELECT &quot;apple&quot; AS article, STRUCT('red' AS color, ['fruit','food'] as type) AS category
  UNION ALL
  SELECT &quot;cabbage&quot; AS article, STRUCT('blue' AS color, ['vegetable', 'food'] as type) AS category
  UNION ALL
  SELECT &quot;book&quot; AS article, STRUCT('red' AS color, ['object'] as type) AS category
  UNION ALL
  SELECT &quot;dog&quot; AS article, STRUCT('green' AS color, ['animal', 'pet'] as type) AS category;
  </code></pre>
  <p>3 - Show that select works (return all rows where category.type contains the value 'food'; these are the rows I want to delete):</p>
  <pre><code>SELECT *
  FROM &lt;DATASET&gt;.&lt;TABLE_NAME&gt;
  LEFT JOIN UNNEST(category.type) type
  WHERE type = 'food'
  </code></pre>
  <p><a href="https://i.stack.imgur.com/3iDmN.jpg" rel="nofollow noreferrer">Initial Result</a></p>
  <p>4 - My attempt at deleting rows where category.type contains 'food' does not work:</p>
  <pre><code>DELETE
  FROM &lt;DATASET&gt;.&lt;TABLE_NAME&gt;
  LEFT JOIN UNNEST(category.type) type
  WHERE type = 'food'
  </code></pre>
  <blockquote>
  <p>Syntax error: Unexpected keyword LEFT at [3:1]</p>
  </blockquote>
  <p><a href="https://i.stack.imgur.com/AGQv0.jpg" rel="nofollow noreferrer">Desired Result</a></p>