title: How do I use BigQuery DML to insert a row with an array within a struct within an array?
body: <p>I'm trying to create a Stack Overflow question where, in order to ask the question, I need to produce a minimal example. As I create the minimal example, I use BigQuery DDL to create a table. The table create step succeeds, inferring that the schema I chose is fine. But the <code>INSERT INTO</code> statement I wrote to insert a row into it causes an error about nested arrays not being allowed.</p>
  <p>The code for creating the table, which succeeds:</p>
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE `my_dataset.test_data_for_so`
  (
    date DATE,
    hits ARRAY&lt;STRUCT&lt;search STRUCT&lt;query STRING, other_column STRING&gt;, metadata ARRAY&lt;STRUCT&lt;key STRING, value STRING&gt;&gt;&gt;&gt;
  );
  </code></pre>
  <p>This is what the schema looks like in the BigQuery GUI after I create the table:</p>
  <p><a href="https://i.stack.imgur.com/pojPv.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/pojPv.png" alt="BigQuery table schema from GUI" /></a></p>
  <p>But when I try to use <code>INSERT INTO</code> to insert a row of data into this table with the following BigQuery DML code:</p>
  <pre class="lang-sql prettyprint-override"><code>INSERT INTO `my_dataset.test_data_for_so` (date, hits)
  VALUES (CAST('2021-01-01' AS date), [STRUCT&lt;query STRING, other_column STRING&gt;('foo bar', 'foo bar'), [STRUCT&lt;key STRING, value STRING&gt;('foo bar', 'foo bar')]])
  </code></pre>
  <p>I get an error:</p>
  <blockquote>
  <p>Cannot construct array with element type ARRAY&lt;STRUCT&lt;key STRING,
  value STRING&gt;&gt; because nested arrays are not supported at [8:103]`.</p>
  </blockquote>
  <p>BigQuery's documentation suggests that this should be possible. They state that arrays within arrays are not possible, but that if you use a struct in between, it's possible. And this is what I believe I'm doing:</p>
  <blockquote>
  <p>BigQuery does not support building arrays of arrays directly. Instead,
  you must create an array of structs, with each struct containing a
  field of type ARRAY.</p>
  </blockquote>
  <p><a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays#building_arrays_of_arrays" rel="nofollow noreferrer">https://cloud.google.com/bigquery/docs/reference/standard-sql/arrays#building_arrays_of_arrays</a></p>